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.

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.

[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
news:[email protected]...
>
> 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]

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

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
news:[email protected]...
>
> 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
>

[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