Google Sheets – How to extract the matching text based on a list

posted in: Technology | 0

Although this is fairly easy to do, I ran into an issue and wanted to share some tips.

Let’s say you want to return the matching text based on an existing list. In my case, I wanted to return the matching Country name based on a full address:

Desired output:

AddressMatching Country
5555 Almere, NetherlandsNetherlands
26, 34485 Sarıyer, TurkeyTurkey

In a separate sheet “countries”, i have a full list of countries like:

Afghanistan
Albania
Algeria
American Samoa
Andorra
etc

So normally the formula should be:

=REGEXEXTRACT(A2,TEXTJOIN("|", 1, countries!A:A))

This didn’t work and I was stumped. I found the problem after various steps of troubleshooting!

Make sure your list is free of special characters like []()=-? etc. For example, check your country list. These are examples that would break your formula:

Congo (republic of congo) or

Congo [See Republic of Congo]

In my case I had brackets, parenthesis, and other chars that broke the regular expression search.