| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
