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
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies