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