Need some Help
 

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.

Need some Help
 

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

Need some Help
 

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

Need some Help
 

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

Need some Help
 

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


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=396328