Separating Numbers and Text with Spaces

I have been googling for a while and can't find something that will exactly fit my needs. I wish I had more experience with excel, then maybe I could write this myself...

Basically what I want to do is take this kind of thing all from one column:

COLA
1 blah blue
green
13 black grey brown

and put it into two columns, one with only the numbers, and the other with only the text.

COLB
1

13

COL C
blah blue
green
black grey brown

Also, I want to be able to then take that data from COL B and C and have it raw so that I can edit it easily by simply selecting row3 in COLB and change it from 13 to 4 (or whatever.)

thank you in advance for the help.

[EDIT] - I prefer a formula solution, but I can do VBA as well. Basically the most simple way of doing this.

The formula for column B is:

=LEFT(A1,FIND(" ",A1)-1)

And for column C:

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

Just copy those down.

Sorry, I missed that. Here you go:

B:
=IF(ISNUMBER(VALUE(LEFT(A1,1))),LEFT(A1,FIND(" ",A1)-1),"")

C:
=IF(ISNUMBER(VALUE(LEFT(A1,1))),RIGHT(A1,LEN(A1)-FIND(" ",A1)),A1)

1.Select cell B1 and paste following formula
=IF(ISNUMBER(FIND(" ",A1,1)),LEFT(A1,FIND(" ",A1,1)-1),"")
2.Select cell C1 and paste following formula
=IF(ISNUMBER(FIND(" ",A1,1)),MID(A1,FIND(" ",A1,1)+1,LEN(A1)),A1)
Now drag the cells down to apply the same formula to cells below as well.

Hope this works

Have a nice time

Chris

Hi

If you will only have a single string of numeric characters in the cell (and without leading zeros) then you could use this:

Column B (number portion)

=IFERROR(LOOKUP(9.9E+307,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1))))),"")

and on col C:

=SUBSTITUTE(A1,B1,"")

Richard