Extracting Country Name from End of String

Extracting Country Name from End of String

Suppose you have a list of addresses, where the country name appears at the end and you need to check whether the name appears in a country table.

This can easily be done, for example, by extracting the last word of the string and using XLOOKUP to verify that this is indeed a valid country name.

 

                   Pic 1: Extract Country Name from End of String

 

But what if the country name consists of two words and not just one?
For example: United States, United Arab Emirates, Ivory Coast, Czech Republic, New Zealand…..?

Then, the previous solution won’t always work.

So, we found a better solution for countries with more than one word in their names.

Pic 2: Country Name consists of one or more words

 

This solution has (at least) three advantages:

1.      It can be applied in any Excel version, not only in Excel 365

2.     As stated above, it can handle countries whose names consist of more than one word

3.    Unlike the first solution, where the country name must be at the end of the string, the second solution allows us to find countries in the middle of the string.