How to split number and text string to separate cells?

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.
 
P

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
 
S

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.
 
Joined
Sep 19, 2013
Messages
1
Reaction score
0
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top