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" wrote:

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" wrote:

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


"Jacob Skaria" wrote:

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


"novastar" wrote:

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.

--
novastar