426,122 Members | 921 Online
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
7 Replies

 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

 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

 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

 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

 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