=LEFT(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))-1)

Line wrap!!!

=LEFT(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)
-LEN(SUBSTITUTE(A1," ",""))))-1)

When the line wraps at an intentional space in a formula it can be
confusing!

Biff

"T. Valko" wrote in message
...
Here's one way:

A1 = try this out 10
A2 = here's another 1
A3 = again 50.5
A4 = last but not least .59

Enter this formula in B1:

=LEFT(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))-1)

Enter this formula in C1:

=SUBSTITUTE(A1,B1&" ","")+0

Select both B1 and C1 then copy down as needed.

Biff

"craezer" wrote in message
...
I am trying to split a column in a product catalog containing text and
numbers so I can further process the numbers. Each cell is consistent in
that
the text is on the left side and numbers on the right. The problem is
that
the quantity of words and numbers may vary from cell to cell. The closest
I've come is using space as a divider in "Text to Columns", but this
results
in more work and concatenating because of the variable cell size.