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 |
Solved! Go to Solution.
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!
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" .
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?
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