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
Solved! Go to 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
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 !
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,
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 !
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], ''), ' ,')