Search code examples
google-sheetsvlookuptext-extraction

Extract a word based on a list of words

So I have sheet with data like this in column B:

130 Oneida St 145 Main St 250 Riberia St

etc. These ares street addresses which have the house number and street name combined.

I have another sheet with list of street names like this in the other sheet's column A Oneida Riberia

I filter the first sheet to only display the streets from the other sheet using:

=REGEXMATCH(B:B, TEXTJOIN("|",TRUE,Sheet2!$A$1:$A))

I want a new column the first sheet with just the street name. Suggestions?

Various formulas - reading examples on here.

Solution

  • You may try:

    =index(ifna(regexextract(B:B,"(?i)\b("&textjoin("|",1,Sheet2!A:A)&")\b")))

    enter image description here