Parsing and removing addresses from a single column

Hello Everyone🖐!

So I was helped with this before but I had a bit of a struggle trying to remove these Canadian zip codes from this column. So far I have this Regex formula \s([\w\s]{7,8})$ , it works great. But I cannot figure out how to remove it from the first column and coming out with my desired outcome.

Billing City / District

land Quebec H8L 0B8

Toronto ON N6D 2C6

West Kelowna BC U2Y 4K5

Vancouver B.C. U7C 6B2

Dorval QC J8L3Z9

Truro NS C3M 7U3

Problematic Outcome I've been getting:

Billing City / District	Billing Zip Code
land Quebec H8L 0B8	H8L 0B8
Toronto ON N6D 2C6	N6D 2C6
West Kelowna BC U2Y 4K5	U2Y 4K5
Vancouver B.C. U7C 6B2	U7C 6B2
Dorval QC J8L3Z9	J8L3Z9
Truro NS C3M 7U3	C3M 7U3

Desired Outcome:

Billing City / District	Billing Zip Code
land Quebec	H8L 0B8
Toronto ON	N6D 2C6
West Kelowna BC	U2Y 4K5
Vancouver B.C.	U7C 6B2
Dorval QC	J8L3Z9
Truro NS	C3M 7U3

Regex

Parse

Help

Accepted answers

CarliE

@nberumen

How about doing something like this with the parse tool -- having 2 capturing groups (.+)([\w\d\s]{7,8})$:

If this solved your problem, please make sure to mark it as a solution!

CarliE

@nberumen

If your original regex was working you can also create a formula after to modify the Billing/ District field or create a new field to do a replace statement like this:

Replace([Billing/ District],[Billing Zip Code], "")

Does that work for you?

All comments

CarliE

@nberumen

How about doing something like this with the parse tool -- having 2 capturing groups (.+)([\w\d\s]{7,8})$:

If this solved your problem, please make sure to mark it as a solution!

nberumen

Hello CarliE!

Thank you for the quick response, I'm having few issues with that regex formula. It's separating London and not pulling all of the area code of the first row, the "C" .

alteryx workflow issue.jpg

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703