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