Extract A String

  • Thread starter Thread starter Joe Gieder
  • Start date Start date
J

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
 
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)
 
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))))
 
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)




.
Click to expand...
 
Back
Top