How to split number and text string to separate cells?

[Lai704]

I have in cells A1:A1000....a combination of number and text, ex.."123 abc abc"

I wanted to split into 2 separate cells...I've done the number part...using
"=LEFT(A1,FIND(" ",A1))" to cell B1

but I can't seem to figure out how to split out "abc abc" to C1?

Any help is appreciated.

Thanks.

 

[Per Jessen]

Hi

Try this in C1:

=RIGHT(A1,LEN(A1)-LEN(B1))

Regards,
Per

 

[Peo Sjoblom]

Your formula is not entirely correct, you have included a trailing space

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


will give 123 as text


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

as number


In C1 you can simply use


=TRIM(SUBSTITUTE(A1,B1,""))


--


Regards,


Peo Sjoblom

 

[FSt1]

hi
try this. it works for your example

=MID(B3,FIND(" ",B3),200)

regards
FSt1

 

[ShaneDevenshire]

Hi,

If you are going to use MID you need to modify the previous example:

=MID(A1,FIND(" ",A1)+1,200)

notice this matches the issue with LEFT

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

and if you want to use right by itself

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

We don't know enough about your data, but the tool of choice for splitting
data is Data, Text to Columns.