Gavin

for data in F19,

=LEFT(F19,FIND("^^",SUBSTITUTE(F19,
","^^",LEN(F19)-LEN(SUBSTITUTE(F19," ",""))))-1)

will collect all except the last word, and

=RIGHT(F19,LEN(F19)-FIND("^^",SUBSTITUTE(F19,
","^^",LEN(F19)-LEN(SUBSTITUTE(F19," ","")))))

will extract the last word.

I presume that you can modify this to cell 1 etc.




Gavin Ross Wrote:
I am trying to seperate some of the data i have in a cell and i am no
quite
sure how to do this, here is an example of what i want to do.

Cell 1 has Los Angeles, CA

I want to seperate Los Angeles and CA into different colums, doe
anyone
know the best way to do this, I have tried using the Text to Colums bu
it
is cutting off some of the data

--
Bryan Hesse
-----------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...fo&userid=2105
View this thread: http://www.excelforum.com/showthread.php?threadid=39632