6

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.

Peter L.
  • 7,276
  • 5
  • 34
  • 53
MrPatterns
  • 4,184
  • 27
  • 65
  • 85

2 Answers2

12

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)

barry houdini
  • 45,615
  • 8
  • 63
  • 81
1

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.

Stepan1010
  • 3,136
  • 1
  • 16
  • 21
  • Hello Stepan - in my version concatenating 1234567890 to A2 ensures that the FIND function never gives you an error (because all digits are found) - so you don't need IFERROR function....and you don't need CTRL+SHIFT+ENTER – barry houdini Feb 27 '13 at 18:40
  • @barry houdini I agree, my version is not optimal. It seems clever to me to use A2&1234567890 instead of just A2 to avoid all the errors in the array generated by FIND(). I did a double take when I first saw it. Only when I focused on the FIND() portion did I see why you did that. Array formulas are not documented well and it seems to me that formulas that produce arrays naturally are documented even less. – Stepan1010 Feb 27 '13 at 19:16