Geek Answers Handbook

How to split / parse a string around the first number in Excel

I have a row in column A, which is a mixture of letters and numbers. I want to split the line in half to the first number, which appears so that "abc123" becomes "abc" in columns B and "123" in column C.

+6
source share
2 answers

If there is any pattern, for example. always 3 letters ..... or only 3 or 4 letters, for example, then you can achieve it more easily, but for any number of letters (provided that the numbers always follow the letters) you can use this formula in B2 (which is simpler, than the proposed formula in topcat3 link, I think)

=LEFT(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&1234567890))-1)

and then this formula in C2

=REPLACE(A2,1,LEN(B2),"")

Note that this last formula returns the numeric part as a text value - if you want it to be numeric, add +0 to the end of the formula (but you will lose all leading zeros)

+11
source

I just wanted to make a small change to Barry's formulas. A little easier to understand, in my opinion, but a little harder to use:

You can use this array formula to find the starting position + 1 of the first number:

 {=MIN(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A2),""))}

entered with ctrl + alt + enter to make it an array formula.

Then you can use this number to split the first part of the string:

 =LEFT(A2,B2-1)

And then you can use REPLACE () to get rid of the first part (letters) of the string.

 =REPLACE(A2,1,LEN(C2),"")

You must accept Barry's answer, not this one, because it is easier to use and more concise. But I just wanted to add variation in my searches to understand how Barry's formula works.

+1
source

More articles:


All Articles