Regex - Parse with delimiters both , and space

[JDong]

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

[messi007]

@JDong,

Please see below:

Attached the workflow,

Hope this helps!

Regards

[jrgo · Accepted solution]

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

[JDong · Accepted solution]

@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 ! 

[messi007]

@JDong,

Strange as I tried with the same version 2020.3 and it works for me

Could you share the input that you're using please?

Attached the workflow with 2020.3 version.

Regards,

[JDong]

@jrgo 

Thanks works well !

One fix needed though

Address	State	Pin	Country
Building Xandu, No.1  Road, BEIJING	BEIJING	100034	CN

Is there a way to clean the data furher where we can remove state from Address field ? This happens to many scenarios in the dataset..Thanks !

[jrgo]

@JDong 

You can add a formula tool after the union to update the [Address] field using the expression below. it's a simple REPLACE() function that replaces any part of the address that matches the parsed out State with nothing. I then nest that in a trim to remove any leading or trailing spaces or commas. This may, however, have a problem if the address actually contains the state so you'll want to see what kind of impact this would have and if it's within your error threshold or not.

TRIM(REPLACE([Address], [State], ''), ' ,')