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

  • Thread starter Thread starter novastar
  • Start date Start date
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.
 
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
 
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.
 
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
 
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?
 
In B1
=SUBSTITUTE(A1,C1,)
In C1
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))

If this post helps click Yes
 
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,""))
 
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.
 
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
 
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.
 
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
 
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.
Click to expand...

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
 
Back
Top