Question: I have some data that looks like this (This is real countries but no sensitive data) It is formatted exactly like i posted

```
Calgary, Alberta T2P 0B3 Canada
Thunder Bay, Ontario P7G 0A1 Canada
Toronto, Ontario M3A 1E5 Canada
Calgary, Alberta T2G 1C6 Canada
Calgary, Alberta T2N 1C2 Canada
Edmonton, Alberta T5J 2W8 Canada
New Westminster, British Columbia V3L 5T4 Canada
London, Ontario N6A 5P6 Canada
Guelph, Ontario N1G 2W1 Canada
Whitby, Ontario L1N 4Z1 Canada
Red Deer, Alberta T4R 0L4 Canada
Edmonton, Alberta T5H 0S2 Canada
West Kelowna, British Columbia V4T 3E3 Canada

```

I am trying to get the Canadian zip code (N6A 5P6, N1G 2W1, etc) in 1 cell (so 1 cell per line with the zipcode. 

So it will look like

```
Original data, zip code
original data, zip code

```

I figured out how to do it for american zip codes, but that space is messing me up. Thank you.

Answer: 
I think this should work, assuming it **always** ends with `Canada`:

```
=MID(A1,SEARCH("Canada",A1)-8,8)

```

[![enter image description here](https://i.stack.imgur.com/Xuphl.jpg)](https://i.stack.imgur.com/Xuphl.jpg)
You could split the content on spaces and just drop the final content of the split (which will always be Canada), preserving the elements at index -2 and -3 (the zip elements)