Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
address1, address2, city, state, zip in one cell; need in 5 cells
I have imported a spreadsheet in which address1, address2, city, state and
zip are all in one cell. Is it possible to use a formula, formulas or processes to relatively easily place the data into separate cells? It's over 20,000 lines of data and we need it in separate columns to upload into our sales database. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
address1, address2, city, state, zip in one cell; need in 5 cells
The Data TextToColumns feature can effectively separate the strings for you.
Give it a try, and post back if you have trouble. Vaya con Dios, Chuck, CABGx3 "puddgyballerina" wrote: I have imported a spreadsheet in which address1, address2, city, state and zip are all in one cell. Is it possible to use a formula, formulas or processes to relatively easily place the data into separate cells? It's over 20,000 lines of data and we need it in separate columns to upload into our sales database. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
address1, address2, city, state, zip in one cell; need in 5 cells
If all of the fields were separated by a single comma (i.e. "123 Main
St,Lenexa,KS,66215"), you could highlight your data and run this macro. Keep in mind that this is not reversible. Sub Delimit_By_Comma() With Selection .TextToColumns Destination:=Selection, DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1)) End With End Sub HTH, JP On Jan 8, 2:57*pm, puddgyballerina wrote: I have imported a spreadsheet in which address1, address2, city, state and zip are all in one cell. *Is it possible to use a formula, formulas or processes to relatively easily place the data into separate cells? *It's over 20,000 lines of data and we need it in separate columns to upload into our sales database. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
address1, address2, city, state, zip in one cell; need in 5 cells
Excel does that with Text to Columns
Tyro "JP" wrote in message ... If all of the fields were separated by a single comma (i.e. "123 Main St,Lenexa,KS,66215"), you could highlight your data and run this macro. Keep in mind that this is not reversible. Sub Delimit_By_Comma() With Selection .TextToColumns Destination:=Selection, DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1)) End With End Sub HTH, JP On Jan 8, 2:57 pm, puddgyballerina wrote: I have imported a spreadsheet in which address1, address2, city, state and zip are all in one cell. Is it possible to use a formula, formulas or processes to relatively easily place the data into separate cells? It's over 20,000 lines of data and we need it in separate columns to upload into our sales database. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
address1, address2, city, state, zip in one cell; need in 5 cells
In Excel 2003 and prior you can use Data/Text to Columns. In Excel 2007,
Data/Data Tools/Text to Columns. Tyro "puddgyballerina" wrote in message ... I have imported a spreadsheet in which address1, address2, city, state and zip are all in one cell. Is it possible to use a formula, formulas or processes to relatively easily place the data into separate cells? It's over 20,000 lines of data and we need it in separate columns to upload into our sales database. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
City State Zip | Excel Discussion (Misc queries) | |||
City State Zip | Excel Worksheet Functions | |||
separate an address street/city,state,zip within a cell? | Excel Worksheet Functions | |||
city, state, zip in same cell | Excel Discussion (Misc queries) | |||
city, state, zip from a single cell to multiple cells | Excel Discussion (Misc queries) |