| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
One More Column Problem/Thank you Stefi, Pete and Dave!
Thank you Stefi, Pete and Dave. Things worked out well. I ran into one more
column problem. I need to separate three pieces of information into separate columns, that is city, state, zip. Here is what my problem looks like. OAK PARK, IL 60302 OAK PARK, IL 603020000 OAK PARK, IL 60302 OAK PARK, IL 60302 OAK PARK, IL 603024272 OAK PARK, IL 60302 OAK PARK, IL 603022609 OAK PARK, IL 603024272 OAK PARK, IL 60304 OAK PARK, IL 603020000 OAK PARK, IL 60302 OAK PARK, IL 60304 OAK PARK, IL OAK PARK, IL 60304 OAK PARK, IL 60302 OAK PARK, IL 60302 OAK PARK, IL 603020000 OAK PARK, IL 60302 OAK PARK, IL OAK PARK, IL 60304 OAK PARK, IL 60302 OAK PARK, IL 60304 OAK PARK, IL 60302 OAK PARK, IL 60302 OAK PARK, IL OAK PARK, IL 60302 OAK PARK, IL 60304 -- Templar |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
One More Column Problem/Thank you Stefi, Pete and Dave!
Highlight your column, go to:
Data - Text to Columns - Delimited - (space). You can also set , as your delimiter and treat consecutive delimiters as one to get rid of the comma. "Templar" wrote: Thank you Stefi, Pete and Dave. Things worked out well. I ran into one more column problem. I need to separate three pieces of information into separate columns, that is city, state, zip. Here is what my problem looks like. OAK PARK, IL 60302 OAK PARK, IL 603020000 OAK PARK, IL 60302 OAK PARK, IL 60302 OAK PARK, IL 603024272 OAK PARK, IL 60302 OAK PARK, IL 603022609 OAK PARK, IL 603024272 OAK PARK, IL 60304 OAK PARK, IL 603020000 OAK PARK, IL 60302 OAK PARK, IL 60304 OAK PARK, IL OAK PARK, IL 60304 OAK PARK, IL 60302 OAK PARK, IL 60302 OAK PARK, IL 603020000 OAK PARK, IL 60302 OAK PARK, IL OAK PARK, IL 60304 OAK PARK, IL 60302 OAK PARK, IL 60304 OAK PARK, IL 60302 OAK PARK, IL 60302 OAK PARK, IL OAK PARK, IL 60302 OAK PARK, IL 60304 -- Templar |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
One More Column Problem/Thank you Stefi, Pete and Dave!
You can use a similar formula to those given to separate out the first
part (treat it as your last name), as you have a comma that determines where to split the data, and you can get the state and zip into another column (if you treat it as your first name). If you then fix the values of the state/zip column, you could use Data | Text-to- columns to split the data using space as the delimiter. Alternatively, the formulae which were looking at your address and splitting on the first space could be applied, but you might have unexpected results where you do not have a zip code (though you can test for this). Hope this helps. Pete On Oct 2, 4:57*pm, Templar wrote: Thank you Stefi, Pete and Dave. *Things worked out well. *I ran into one more column problem. *I need to separate three pieces of information into separate columns, that is city, state, zip. *Here is what my problem looks like. OAK PARK, *IL 60302 OAK PARK, *IL 603020000 OAK PARK, *IL 60302 OAK PARK, *IL 60302 OAK PARK, *IL 603024272 OAK PARK, *IL 60302 OAK PARK, *IL 603022609 OAK PARK, *IL 603024272 OAK PARK, *IL 60304 OAK PARK, *IL 603020000 OAK PARK, *IL 60302 OAK PARK, *IL 60304 OAK PARK, *IL OAK PARK, *IL 60304 OAK PARK, *IL 60302 OAK PARK, *IL 60302 OAK PARK, *IL 603020000 OAK PARK, *IL 60302 OAK PARK, *IL OAK PARK, *IL 60304 OAK PARK, *IL 60302 OAK PARK, *IL 60304 OAK PARK, *IL 60302 OAK PARK, *IL 60302 OAK PARK, *IL OAK PARK, *IL 60302 OAK PARK, *IL 60304 -- Templar |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
One More Column Problem/Thank you Stefi, Pete and Dave!
That would put OAK in one column and PARK in another.
Pete On Oct 2, 5:04*pm, Sean Timmons wrote: Highlight your column, go to: Data - Text to Columns - Delimited - (space). You can also set , as your delimiter and treat consecutive delimiters as one to get rid of the comma.. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
One More Column Problem/Thank you Stefi, Pete and Dave!
Boo me..
In B2: =LEFT(A2,FIND(",",A2)-1) in C2: =MID(A2,FIND(",",A2)+2,2) in D2: =RIGHT(A2,LEN(A2)-FIND(" ",A2,FIND(",",A2)+2)) "Pete_UK" wrote: That would put OAK in one column and PARK in another. Pete On Oct 2, 5:04 pm, Sean Timmons wrote: Highlight your column, go to: Data - Text to Columns - Delimited - (space). You can also set , as your delimiter and treat consecutive delimiters as one to get rid of the comma.. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
One More Column Problem/Thank you Stefi, Pete and Dave!
Could you give me the formulas as you did in my previous post?
-- Templar "Pete_UK" wrote: You can use a similar formula to those given to separate out the first part (treat it as your last name), as you have a comma that determines where to split the data, and you can get the state and zip into another column (if you treat it as your first name). If you then fix the values of the state/zip column, you could use Data | Text-to- columns to split the data using space as the delimiter. Alternatively, the formulae which were looking at your address and splitting on the first space could be applied, but you might have unexpected results where you do not have a zip code (though you can test for this). Hope this helps. Pete On Oct 2, 4:57 pm, Templar wrote: Thank you Stefi, Pete and Dave. Things worked out well. I ran into one more column problem. I need to separate three pieces of information into separate columns, that is city, state, zip. Here is what my problem looks like. OAK PARK, IL 60302 OAK PARK, IL 603020000 OAK PARK, IL 60302 OAK PARK, IL 60302 OAK PARK, IL 603024272 OAK PARK, IL 60302 OAK PARK, IL 603022609 OAK PARK, IL 603024272 OAK PARK, IL 60304 OAK PARK, IL 603020000 OAK PARK, IL 60302 OAK PARK, IL 60304 OAK PARK, IL OAK PARK, IL 60304 OAK PARK, IL 60302 OAK PARK, IL 60302 OAK PARK, IL 603020000 OAK PARK, IL 60302 OAK PARK, IL OAK PARK, IL 60304 OAK PARK, IL 60302 OAK PARK, IL 60304 OAK PARK, IL 60302 OAK PARK, IL 60302 OAK PARK, IL OAK PARK, IL 60302 OAK PARK, IL 60304 -- Templar |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
One More Column Problem/Thank you Stefi, Pete and Dave!
Sean has done so just before you posted - he assumes your data is in
column A, so you will need to change the references in the formulae to suit your layout. Pete On Oct 2, 5:51*pm, Templar wrote: Could you give me the formulas as you did in my previous post? -- Templar |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pete UK previous solution can't get it to work | Excel Discussion (Misc queries) | |||
| Dave Peterson | Excel Discussion (Misc queries) | |||
| to Dave Peterson | Excel Discussion (Misc queries) | |||
| Dave just one more question | Excel Discussion (Misc queries) | |||
| Dave My New Macro | Excel Worksheet Functions | |||
Linear Mode
