Gavin,

This is a Formula, and you copy it straight to the cell that you need
the answer in.

If you get a Syntax error then check for spaces etc.

for the last word from F19 - use
=RIGHT(F19,LEN(F19)-FIND("^^",SUBSTITUTE(F19,"
","^^",LEN(F19)-LEN(SUBSTITUTE(F19," ","")))))

for the other words from F19 use -
=LEFT(F19,FIND("^^",SUBSTITUTE(F19,"
","^^",LEN(F19)-LEN(SUBSTITUTE(F19," ",""))))-1)

If you still have a problem, do a search on posts by me and extract the
Zip file from my Pie chart question, this formula is in there.

There is a 'Search this Forum' option at
http://www.excelforum.com/forumdisplay.php?f=5




Gavin Ross Wrote:
If I run this as a macro I get a syntax error. What is the easiest way
to do
this?

Thanks
Gavin...

"Bryan Hessey"

wrote in message
...

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
not
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, does
anyone
know the best way to do this, I have tried using the Text to Colums
but
it
is cutting off some of the data.


--
Bryan Hessey

------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=396328



--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=396328