CODEWITHSUNDEEP
excelsplitexcel-formula
MrPatterns
MrPatterns

Reputation: 4434

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

I have a string in column A that is a mixture of letters and numbers. I want to split the string in half before the first number that shows up such that "abc123" becomes "abc" in column B and "123" in column C.

Upvotes: 6

Views: 20364

Answers (2)

Stepan1010
Stepan1010

Reputation: 3136

Just wanted to contribute a slight variation on Barry's formulas. Slightly easier to understand in my opinion but a little bit more difficult 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 that number to split the first part off of the string:

=LEFT(A2,B2-1)

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

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

You should accept Barry's answer and not this one because his is easier to use and more concise. But just wanted to add a variation in my quest to understand how Barry's formula worked.

Upvotes: 1

barry houdini
barry houdini

Reputation: 46371

If there's any sort of pattern, e.g. always 3 letters.....or only 3 or 4 letters, for example then you can achieve more easily but for any number of letters (assuming that numbers always follow letters) you can use this formula in B2 (which is simpler than the suggested formula in topcat3's 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 number part as a text value - if you want it to be numeric add +0 to end of formula (but you will lose any leading zeroes)

Upvotes: 12

Related Questions