Splitting of Name & Address from same oclumn

Is there anyway else to format the address in Alteryx.

I have an address column which includes Name & Address all combined into ingle column as example below. I needed it to be split into multiple columns, one column each for Name, Address, City, State, Zip. #Designer #new2Alteryx #helpneeded

Address

Leather & CO (SP100), Draxwell, 1134 PArk Ave Apt 133, New York NY 10218,,,

May & CO, 3050 Peach Road, Atlanta GA 30301,,,,

John Smith, c/o Jack SImth, 3050 Link Road, Boston MA 30301,,,

Desired Output

Name	Address	City	State	Zipcode
Leather & CO (SP100), Draxwell	134 PArk Ave Apt 133	New York	NY	10218
May & CO	3050 Peach Road	Atlanta	GA	30301

Developer

Developer Tools

Accepted answers

flying008

Hi, @kafka_u21

FYI.

^(.+?)(?=\,\s\d)\,\s([^\,]+)\,\s?([[:alpha:]\s]+)\s([[:alpha:]]{2,})\s(\d{5})\,*$

录制_2023_08_02_09_39_43_4.gif

All comments

jdminton

Sometimes it’s better to do these things backwards. Try reversing the string to parse by comma back through the street number. Then reverse back to make it readable. I’m on mobile right now, but essentially a formula tool to reverse the string, text to columns or regex to parse by commas. Then formula tool to reverse string back. After that, you can parse the city state in a way that makes sense (maybe even reversed since some cities have two words but no states do);)

flying008

Hi, @kafka_u21

FYI.

^(.+?)(?=\,\s\d)\,\s([^\,]+)\,\s?([[:alpha:]\s]+)\s([[:alpha:]]{2,})\s(\d{5})\,*$

录制_2023_08_02_09_39_43_4.gif

kafka_u21

Hi flying008,

possible to upload the workflow, i tried using the same regex but all the columns come as blank for me.

kafka_u21

i got it. the solution works. Thanks so much flying008

kafka_u21

@flying008 , found myself in a spot of bother here. the previous formula worked great for the initial set of records but not most. below is the scenario the regex formula is failing, possible to help out?. I needed to split them into separate columns for Address, City, State, Zipcode

1070 PARK AVE APT 11A,NEW YORK NY 10211,,

8061 PEACHTREE ROAD, N.W.,SUITE 101,ATLANTA GA 60321,

100 SOUTH POINTE BEACH, APT.205,MIAMI BEACH FL 33141

1141 5TH AVE APT 2B,NEW YORK NY 10211

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703