Regex - Parse with delimiters both , and space

Hi,

56 North Rock Maple Ave.Miami Beach, FL 33139
8929 New Street Chevy Chase, MD 20815
14 James Drive Milton, MA 02186
9995C E. Holly Ave. Reading, MA 01867
7780 Thompson Rd. Muncie, IN 47302
416 Carriage Ave. Baldwinsville, NY 13027
7814 Sunnyslope Street Burbank, IL 60459
772 Oklahoma St. Dorchester, MA 02125
392 High Noon Avenue Port Charlotte, FL 33952
43 Lexington Avenue Little Falls, NJ 07424

De Yang Shi Chang Jiang Dong Lu,Sichuan, Sichuan,100700 CN
Chang Jiang Lu Gong Hua,Dalianshi, Dalianshi, 1283749 CN
Capital Stone Works Ltd, 36 Druid St, London, SE1 2HH
Pennard Vets,Unit 4, Kelly Corner Shopping Development, Belfast, BT12 7AA
5603 Town St Ste 200E, SAN FRANCISCO, CA, 94103-5690 US

I have the above data in my column and want to parse and split the data into multiple columns.

Output :

Col 1	State	Pin	Country
56 North Rock Maple Ave.Miami Beach	FL	33139
8929 New Street Chevy Chase	MD	20815
14 James Drive Milton	MA	2186
9995C E. Holly Ave. Reading	MA	1867
7780 Thompson Rd. Muncie	IN	47302
De Yang Shi Chang Jiang Dong Lu	Sichuan	100700	CN
Chang Jiang Lu Gong Hua	Dalianshi	1283749	CN
Capital Stone Works Ltd, 36 Druid St	London	SE1 2HH
Pennard Vets,Unit 4, Kelly Corner Shopping Development	Belfast	BT12 7AA
5603 Town St Ste 200E	SAN FRANCISCO,CA	94103-5690	US

Is it possible to achieve this ? Thanks

Datasets

Developer

Developer Tools

Accepted answers

jrgo

Hi @JDong

I wasn't able to come up with a single REGEX to parse out the different patterns found, but was able to narrow it down to only 2. The Filter first splits up the records with those that end with a Space followed by 2 chars, which will be assumed as a record that contains the country.

I believe this properly parsed out to your requirements, but if the pattern deviates significantly outside of the examples you provided, it may improperly parse out the address parts.

Hope this helps!

Jimmy

DigitMac.yxmd

JDong

@messi007

Thanks a lot for your kind support !

For some reason in actual scenario

The first Regex does not show values in he new columns but NULL

But it works towards end of the flow

In the select tool marked #1, the values show up in the new columns but the end result is not correct. I also checked the field and it is of V_String datatype.

Any thoughts here please and I am using 2020.3 version of Alteryx. Thanks !

All comments

messi007

@JDong,

Please see below:

Attached the workflow,

Hope this helps!

Regards

RegEx_20210426.yxmd

jrgo

Hi @JDong

I believe this properly parsed out to your requirements, but if the pattern deviates significantly outside of the examples you provided, it may improperly parse out the address parts.

Hope this helps!

Jimmy

DigitMac.yxmd

JDong

@messi007

Thanks a lot for your kind support !

For some reason in actual scenario

The first Regex does not show values in he new columns but NULL

But it works towards end of the flow

In the select tool marked #1, the values show up in the new columns but the end result is not correct. I also checked the field and it is of V_String datatype.

Any thoughts here please and I am using 2020.3 version of Alteryx. Thanks !

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703