By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,122 Members | 921 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,122 IT Pros & Developers. It's quick & easy.

First two numbers in string extracting?

100+
P: 125
I have strings like, only some examples:


ABC 213/23213,23
ABC 213/23213,DSF
CVNCVB 3456/324
XCVV 123 /234/324

I need to take out the two first numbers.

213 and 23213
213 and 23213
3456 and 324
123 and 234

Is it a simple way to do this in excel or do I have to do something like a regular expression?

There is always the "/" delimiter between the numbers sometimes a extra space before delimiter.



Excel 2003 vba macro
Dec 22 '08 #1
Share this Question
Share on Google+
7 Replies


Delerna
Expert 100+
P: 1,134
I assume each string is in a single cell in excel
To get the first number you could use this formula

=RIGHT( LEFT(A1,SEARCH("/",A1&",")-1), LEN(LEFT(A1,SEARCH("/",A1&",")-1)) - SEARCH(" ", LEFT(A1,SEARCH("/",A1&",")-1) ) )


where A1 is the cell containing the string first string
Just pull the formula down to get the rest
Dec 22 '08 #2

Delerna
Expert 100+
P: 1,134
To get the second number you could use this formula

=MID(A1,SEARCH("/",A1)+1,SEARCH(",",A1&",") - SEARCH("/",A1)-1)


The only problem here is the string with two backslashes
I'll leave you to figure that one out :)


Actually, on looking at it, you may even be able to use something like the second formula to get the first number also ;)
Dec 22 '08 #3

100+
P: 125
@Delerna
Thx I will try that


I was thinking of using split on "/" then trim an then extract number but i got into problem with other things so i don't know if it works as I was thinking.
Dec 22 '08 #4

100+
P: 125
I managed to solve it with split and trim functions. I had to do different splits depending on what the strings started with. But now it works, now I only need to whrite som conditions :)
Dec 22 '08 #5

NeoPa
Expert Mod 15k+
P: 31,419
Nice :)

If you're using Split() already you don't need me to jump in.
Dec 22 '08 #6

Delerna
Expert 100+
P: 1,134
hmmm, excel has split? I couldn't find it the help, I might have to look a bit closer :)
Dec 22 '08 #7

NeoPa
Expert Mod 15k+
P: 31,419
It won't work as a worksheet function. It's only available from within VBA.

You can call a function within a worksheet formula though ;)
Dec 22 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.