Separate Numbers from Text

  • Thread starter Thread starter J.Scott
  • Start date Start date
J

J.Scott

I often import data files into Excel 97 and in one column it will have
numbers and text i.e.

12345JONES
12369BUTCH
12377SMITH
12481LENNO

What I would like to be able to do is separate (possibly with the use of 2
helper columns) the numbers from the text, so I would have the numbers in
one column and the text in another column.

Any help greatly appreciated.

J.Scott
 
Hi

do you always have 5 digits at the beginning. If yes you may use the
function 'Data - Text to columns'
- select your column
- choose the menu 'Text to columns'
- use 'fixed length'
 
Text to Columns works but you could avoid the wizard with the 2 helper
columns you mentioned - this would also keep your source info intact
in the event that's ever useful

with column A as your source, you'd use the following formulas in
columns B and C

=left(a1,5)
=right(a1,(len(a1)-5)

6 or one, 1/2 dozen of the other

cheers,
Brian
 
Back
Top