Home

Separating an address in one column to columns: address1, address2, city, state

%3CLINGO-SUB%20id%3D%22lingo-sub-644673%22%20slang%3D%22en-US%22%3ESeparating%20an%20address%20in%20one%20column%20to%20columns%3A%20address1%2C%20address2%2C%20city%2C%20state%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644673%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20separate%20a%20column%20listing%20addresses%20into%204%20columns%2C%20address1%2C%20address2%2C%20city%2C%20state.%20Text%20to%20column%20does%20not%20separate%20because%20there%20is%20not%20any%20comma's%20only%20spaces%20and%20I%20cannot%20separate%20by%20spaces%20because%20there%20are%20required%20spaces%20in%20each%20part%20of%20the%20address%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-644673%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-654909%22%20slang%3D%22en-US%22%3ERe%3A%20Separating%20an%20address%20in%20one%20column%20to%20columns%3A%20address1%2C%20address2%2C%20city%2C%20state%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-654909%22%20slang%3D%22en-US%22%3EThere%20must%20be%20some%20delimiter%20for%20each%20part%20of%20the%20address%3B%20otherwise%2C%20no%20logic%20would%20explain%20how%20the%20address%20would%20be%20separated%20into%20parts.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-655485%22%20slang%3D%22en-US%22%3ERe%3A%20Separating%20an%20address%20in%20one%20column%20to%20columns%3A%20address1%2C%20address2%2C%20city%2C%20state%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-655485%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F348364%22%20target%3D%22_blank%22%3E%40Diane307%3C%2FA%3E%26nbsp%3BNot%20sure%20of%20this%20will%20help%2C%20I%20have%20assumed%20that%20some%20parts%20of%20your%20addresses%20start%20with%20capital%20letters%2C%20the%20attached%20file%20shows%20how%20to%20identify%20each%20capitol%20letter%20in%20the%20address%2C%20get%20its%20location%20in%20the%20string%20then%20extract%20the%20remaining%20lower%20case%20letters.%20of%20course%20if%20you%20don't%20have%20any%20capitols%20then%20life%20becomes%20very%20difficult%2C%20if%20not%20impossible.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERich%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Diane307
Occasional Visitor

I need to separate a column listing addresses into 4 columns, address1, address2, city, state. Text to column does not separate because there is not any comma's only spaces and I cannot separate by spaces because there are required spaces in each part of the address

2 Replies
There must be some delimiter for each part of the address; otherwise, no logic would explain how the address would be separated into parts.

Hi @Diane307 Not sure of this will help, I have assumed that some parts of your addresses start with capital letters, the attached file shows how to identify each capitol letter in the address, get its location in the string then extract the remaining lower case letters. of course if you don't have any capitols then life becomes very difficult, if not impossible.

 

Rich 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies