Parsing and removing addresses from a single column

[nberumen]

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

[CarliE · Accepted solution]

@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" . 

[CarliE · Accepted solution]

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

[nberumen]

Thank you Carli!

This helped out a lot, I used the first formula you have me on a different sheet and it worked wonderfully. Then your second suggestion helped me with my original issue.

Thanks Again!

nberumen

[CarliE]

@nberumen 

No problem!! Glad I can help :)