Separate Numbers from Text

[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

 

[Frank Kabel]

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'

 

[J.Scott]

Yup - always 5 digit numbers.

I'll try that Frank - many thanks

J.S.

 

[Brian Kenny]

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

 

[J.Scott]

Again many thanks - they both work, but good to have 2 options on hand.

J.S.