First two numbers in string extracting?

[asedt]

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

[Delerna]

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,SEA RCH("/",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

[Delerna]

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 ;)

[asedt]

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 ;)

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.

[asedt]

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 :)

[NeoPa]

Nice :)

If you're using Split() already you don't need me to jump in.

[Delerna]

hmmm, excel has split? I couldn't find it the help, I might have to look a bit closer :)

[NeoPa]

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 ;)