How to split number and text string to separate cells?

  • Thread starter Thread starter Lai704
  • Start date Start date
L

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.
 
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
 
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.
 
i have a column like gj1eh7538
and i want ans like gj | 1 |eh | 7538
here | indicates new column

******if it possible than pls send me formula /query to (e-mail address removed)
 
Back
Top