Need some Help

[Gavin Ross]

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]

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]

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

Thanks
Gavin...

"Bryan Hessey" <[email protected]>
wrote in message

 

[Bryan Hessey]

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

 

[mudraker]

Gavin

When you tryed to seperate your example into 2 different columns did
you use
Text to Columns, Fixed Width
or
Text to Columns, Delimited

If all your entries have a , where you want to split the entry then you
need to use Text to Columns, Delimited, with a tick in the Comma box