separating text and numbers in a column

**maximpinto** · 05-28-2009, 11:12 PM

Hi,
I want to separate text and numbers in a column.
I get reports say for example in Column A.
All have a standard format SR0000 (first2 being Alphabets"SR" and rest 4 being numbers)
I want to separate SR from the numbers. How to achieve this.
Thanks in advance
Max

**Jbentley** · 05-29-2009, 01:22 AM

by separate text an numbers in a column I'm guessing you're looking for a solution which results in the text in one column and the numbers in another column...

Let's assume you have SR1000 in Cell A2.

In Cell B2 enter the following: =left(A2,2)
The result will be first two characters (starting from the LEFT hand end of the string in A2)

In Cell C2 enter the following: =ABS(RIGHT(A2,LEN(A2)-2)
The result will be all the characters from the string in cell A2 EXCEPT the first two characters (starting from the LEFT hand end of the string in A2)
The result will be a number [the ABS function in Excel is supposed to be used for making numbers Absolute - changing all positive and negative numbers to positive - but also works as a converter of text to positive numbers

**DonkeyOte** · 05-29-2009, 01:32 AM

If the split is always in the same position (Char 3) you could insert a new blank column after the column containing your strings, then use:

Data -> Text to Columns -> Fixed Width -> set split after SR -> click Finish (see note below)

If you want 0000 to stay as 0000 and not become 0 then set the 2nd column to be Text in Step 3 before clicking Finish (or apply Custom Format to number column of 0000)