How to separate text and numbers in one cell or column?

N

novastar

Hi,

I have a column of cells which are in any of the following formats:

x #
x x #
x x x #

where x could be any number of characters (a-z or A-Z) either upper or lower
case.
# could be a 1,2 or 3 digit number 0-9

I generally have to post process this file in UNIX before I import it into
excel and do some modifications.

Is there an easier way to do this in Excel? I don't mind copying the column
and post processing one column to retain numbers and another column to retain
text.
 
J

Jacob Skaria

With your text in cell A1; try this formula in B1 .Please note that this is
an array formula. Within the cell in edit mode (F2) paste this formula and
press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar'
you can notice the curly braces at both ends like "{=<formula>}"

=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),99)

If this post helps click Yes
 
T

T. Valko

This will extract the last "word" in a cell:

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",255)),255))

Note that the result is a *TEXT* string even if the last "word" in the cell
is a number. If your numbers can contain leading 0s then you'll need to
extract them as text in order to retain the leading 0s.
 
J

Jacob Skaria

The previous one will return an error for a lenghty string. Try this array
formula
=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$99),1)),0),99)

If this post helps click Yes
 
N

novastar

Hi T.Valco,

Thanks........this helps me get the numbers in a column..

Now....I would like to eliminate the numbers and extract only the text in a
different column...how do i do this?
 
J

Jacob Skaria

In B1
=SUBSTITUTE(A1,C1,)
In C1
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))

If this post helps click Yes
 
T

T. Valko

Let's assume you have this formula to extract the numbers in cell B1:

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",255)),255))

Enter this formula in C1 to return only the text portion of cell A1:

=TRIM(SUBSTITUTE(A1,B1,""))
 
N

novastar

Hi Jacob,

This did not work for me. If I have a cell containing "Adam 22", I would
like to separate "adam" in one column and "22" in another column.

With the formula below I get "#N/A" in the cell.
 
J

Jacob Skaria

You are entering the formula as normal. Please note that this is
an array formula. Within the cell in edit mode (F2) paste this formula and
press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar'
you can notice the curly braces at both ends like "{=<formula>}"

If this post helps click Yes
 
N

novastar

Hi Jacob,

Thanks....this helps.

Thanks to both Jacob and T.Valko...you helped me fix an issue I have been
struggling with for years. Now I don;t have to go to UNIX and stay in
excel...:)

Thanks again.
 
J

JP Ronse

Hi,

You could try 'text to columns' ... and save it as csv.

But if you have to post-process it with unix, why don't making a shell
script that does it all?

I suppose it is a 'text' file like most unix files are:
- sed 's/ /,/g' file1 > file2 will replace the spaces by ","

Keep in mind that Unix is 7-bit oriented ans Windows/Dos is 8:
dos2unix file > file1 ( I think, use man dos2unix in unix environment)

What is the added value of post processing in Unix?

If you don't mind to send me a sample and explaining what your outcome must
be, I will have a look into.

(e-mail address removed)

Wkr,

JP
 
N

novastar

Hi Jacob,

My bad. I was wrong. I did what you said and it works just fine.

Thanks much!
 
R

Ron Rosenfeld

Hi,

I have a column of cells which are in any of the following formats:

x #
x x #
x x x #

where x could be any number of characters (a-z or A-Z) either upper or lower
case.
# could be a 1,2 or 3 digit number 0-9

I generally have to post process this file in UNIX before I import it into
excel and do some modifications.

Is there an easier way to do this in Excel? I don't mind copying the column
and post processing one column to retain numbers and another column to retain
text.

If the text always comes first, and the number last, then

For text:

=TRIM(LEFT(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1,2,3,4,5,6,7,8,9,0"))-1))

For the numeric portion (with the number returned as text, which would include
leading zero's):

=MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1,2,3,4,5,6,7,8,9,0")),99)

or, to return the value as numeric:

=--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1,2,3,4,5,6,7,8,9,0")),99)

--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top