On Fri, 21 Aug 2009 09:37:01 -0700, 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.

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