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:
| Address | Matching Country |
| 5555 Almere, Netherlands | Netherlands |
| 26, 34485 Sarıyer, Turkey | Turkey |
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.