Extract A String

[Joe Gieder]

I have this in cell A1 "5 207420 GROMMET", How would I
get it in to three separate cells. I have tried:
=LEFT(A1,FIND(" ",A1)-1)
=RIGHT(A1,FIND(" ",A1))
=MID(A1,FIND(" ",A1))

I have several thousand rows of data and they are all
formated this way.

Thanks for you help as always
Joe

 

[Bob Phillips]

Joe,

Try these

=LEFT(A1,FIND(" ",A1)-1)
=MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,99)
=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Dave R.]

A1=5 207420 GROMMET

A4 =LEFT(A1,FIND(" ",A1)-1)
A5 =MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)
A6 =RIGHT(A1,LEN(SUBSTITUTE(A1," ",""))-SUMPRODUCT(--(LEN(A4:A5))))

 

[Joe Gieder]

Thanks Bob. It works great.

Joe

-----Original Message-----
Joe,

Try these

=LEFT(A1,FIND(" ",A1)-1)
=MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,99)
=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND (" ",A1)-1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)




.

 

[Gord Dibben]

Joe

Try Data>Text to Columns>Space de-limited.

Gord Dibben Excel MVP