Menu
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.
332
Extract first n characters from string Select a blank cell, here I select the Cell G1, and type this formula =LEFT(E1,3) (E1 is the cell you want to extract the first 3 characters from), press Enter button, and drag fill handle to the range you want. Then you see the first 3 characters are extracted.
Select the cells you want to divide, navigate to the Data tab > Data Tools group, and click the Text to Columns button. In the first step of the Convert Text to Columns wizard, you choose how to split cells - by delimiter or width.
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)
117
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
