Home

Delimiting Excel Addresses

%3CLINGO-SUB%20id%3D%22lingo-sub-770387%22%20slang%3D%22en-US%22%3EDelimiting%20Excel%20Addresses%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-770387%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20list%20of%2019%2C000%2B%20addresses%20all%20in%20one%20cell.%20I%20need%20the%20information%20delimited%20into%20the%20following%20columns%3A%20Address%20line%201%2C%20Address%20Line%202%2C%20City%2C%20State%2C%20Zip%20Code.%20Unfortunately%2C%20the%20format%20of%20the%20addresses%20vary%20from%20line%20to%20line.%20Some%20use%205-digit%20zip%20codes%2C%20others%209-digit.%20Some%20have%20been%20separated%20by%20commas%2C%20other%20no%20separation.%20Some%20also%20do%20not%20have%20the%20need%20for%20Address%20Line%202%20(i.e.%20Suite%20100).%20This%20make%20the%20standard%20%22Text%20to%20Columns%22%20features%20void%20of%20power.%20What%20other%20address%20recognition%20in%20excel%20could%20be%20used%20to%20delimit%20the%20data%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-770387%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-770456%22%20slang%3D%22en-US%22%3ERe%3A%20Delimiting%20Excel%20Addresses%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-770456%22%20slang%3D%22en-US%22%3EIt%20seems%20you%20may%20initially%20employ%20Text%20to%20Columns%20with%20space%20as%20delimiter%2C%20whereafter%20you%20decipher%20your%20next%20action.%3C%2FLINGO-BODY%3E
MTucker
Occasional Visitor

I have a list of 19,000+ addresses all in one cell. I need the information delimited into the following columns: Address line 1, Address Line 2, City, State, Zip Code. Unfortunately, the format of the addresses vary from line to line. Some use 5-digit zip codes, others 9-digit. Some have been separated by commas, other no separation. Some also do not have the need for Address Line 2 (i.e. Suite 100). This make the standard "Text to Columns" features void of power. What other address recognition in excel could be used to delimit the data?

1 Reply
It seems you may initially employ Text to Columns with space as delimiter, whereafter you decipher your next action.
Related Conversations
Copy/paste no longer working in Excel
Jon Firooz in Excel on
76 Replies
changing Date format in a pivot table
ahmad ali in Excel on
12 Replies
runtime error 1004 : Method range of object - 'Global' failed
Lorenzo Kim in Excel on
16 Replies
INDEX & MATCH interrupted by Inconsistent Dates
shade206 in Excel on
18 Replies
!SPILL error with SUMIFS formula
Dzung Vu in Excel on
18 Replies
Need to extract values from named cells (Get and Transform)
JEMagnussen in Excel on
23 Replies