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

Asked By novastar on 21-Aug-09 12:37 PM
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.

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 do not mind copying the column
and post processing one column to retain numbers and another column to retain
text.

--
novastar




Jacob Skaria replied on 21-Aug-09 12:44 PM
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
---------------
Jacob Skaria
T. Valko replied on 21-Aug-09 12:54 PM
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 will need to
extract them as text in order to retain the leading 0s.

--
Biff
Microsoft Excel MVP
Jacob Skaria replied on 21-Aug-09 01:03 PM
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
---------------
Jacob Skaria
novastar replied on 21-Aug-09 01:31 PM
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?
--
novastar
Jacob Skaria replied on 21-Aug-09 01:36 PM
In B1
=SUBSTITUTE(A1,C1,)
In C1
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))

If this post helps click Yes
---------------
Jacob Skaria
T. Valko replied on 21-Aug-09 01:41 PM
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,""))

--
Biff
Microsoft Excel MVP
novastar replied on 21-Aug-09 01:44 PM
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.

--
novastar
Jacob Skaria replied on 21-Aug-09 01:49 PM
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
---------------
Jacob Skaria
novastar replied on 21-Aug-09 01:57 PM
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.
--
novastar
JP Ronse replied on 21-Aug-09 02:08 PM
Hi,

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

But if  you have to post-process it with unix, why do not 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 do not mind to send me a sample and explaining what your outcome must
be, I will have a look into.

jean-pierre.degroote@skynet.be

Wkr,

JP
novastar replied on 21-Aug-09 02:13 PM
Hi Jacob,

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

Thanks much!
--
novastar
Ron Rosenfeld replied on 21-Aug-09 10:04 PM
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
Bibhu Bag replied to Jacob Skaria on 19-Apr-12 04:39 AM
Hi Jacob thanks a lot u r Genius man thx so much