Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Parse ST-ZIP Cell (15,427 times...)
Thank you all for earlier help. I am now parsing a column of 15,427 records containing combined State and Zip Code data, IE: A 1 NY 10023 And I need to parse that data into two separate columns. I was using the Macro funtion earlier for some successful endeavors, but the tiny Macro Toolbar "Stop Recording / Reference" has disappeared. 1) Can anyone help me with the trick to that bloody toolbar, and 2) Is a Macro the best way to accomplish such a parsing... (With one empty new column to the right, I was going to go into cell edit mode, Cut the Zip, delete the three spaces to the R of the State, Paste the Zip in the new Column to the right, and decend one Row... or something to that effect). Thank you so much, folks. -- jawdawson ------------------------------------------------------------------------ jawdawson's Profile: http://www.excelforum.com/member.php...o&userid=30648 View this thread: http://www.excelforum.com/showthread...hreadid=503121 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Parse ST-ZIP Cell (15,427 times...)
Put the following formula in the column B & C, they will provide u the
state in the column B & zip in the column without any spaces B C =TRIM(LEFT(A1,2)) & =TRIM(RIGHT(A1,5)) NY 10023 Hope the above solution will help u. --- Selvarathinam. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Parse ST-ZIP Cell (15,427 times...)
How about using DataText to ColumnsDelimited by space?
Gord Dibben MS Excel MVP On Thu, 19 Jan 2006 17:07:30 -0600, jawdawson wrote: Thank you all for earlier help. I am now parsing a column of 15,427 records containing combined State and Zip Code data, IE: A 1 NY 10023 And I need to parse that data into two separate columns. I was using the Macro funtion earlier for some successful endeavors, but the tiny Macro Toolbar "Stop Recording / Reference" has disappeared. 1) Can anyone help me with the trick to that bloody toolbar, and 2) Is a Macro the best way to accomplish such a parsing... (With one empty new column to the right, I was going to go into cell edit mode, Cut the Zip, delete the three spaces to the R of the State, Paste the Zip in the new Column to the right, and decend one Row... or something to that effect). Thank you so much, folks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Parse ST-ZIP Cell (15,427 times...)
Both suggestions helpful - thank you. Text to Columns was magic. Thank you both. -- jawdawson ------------------------------------------------------------------------ jawdawson's Profile: http://www.excelforum.com/member.php...o&userid=30648 View this thread: http://www.excelforum.com/showthread...hreadid=503121 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Parse ST-ZIP Cell (15,427 times...)
If you use Data, Text to columns
be sure to specify within the Text to columns that the second column is text. Formatting the column as text beforehand is not good enough. Many east coast zip codes have a leading zero. --- "Gord Dibben" <gorddibbATshawDOTca wrote ... How about using DataText to ColumnsDelimited by space? Gord Dibben MS Excel MVP On Thu, 19 Jan 2006 17:07:30 -0600, jawdawson wrote: 1 NY 10023 And I need to parse that data into two separate columns. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Parse ST-ZIP Cell (15,427 times...)
Good point David.
Thanks, Gord On Fri, 20 Jan 2006 11:38:46 -0500, "David McRitchie" wrote: If you use Data, Text to columns be sure to specify within the Text to columns that the second column is text. Formatting the column as text beforehand is not good enough. Many east coast zip codes have a leading zero. --- "Gord Dibben" <gorddibbATshawDOTca wrote ... How about using DataText to ColumnsDelimited by space? Gord Dibben MS Excel MVP On Thu, 19 Jan 2006 17:07:30 -0600, jawdawson wrote: 1 NY 10023 And I need to parse that data into two separate columns. Gord Dibben MS Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying cell names | Excel Discussion (Misc queries) | |||
Cell color based upon cell value | Excel Discussion (Misc queries) | |||
Count number of times a specific number is displayed in a cell ran | Excel Worksheet Functions | |||
enter a time into a cell, have the cell show two times the entry | Excel Worksheet Functions | |||
Copy from worksheet to another x times | Excel Discussion (Misc queries) |