How do I strip out everything from an address except street name?

Question

I am trying to return the street name from an address that is a string of text.

So from this: 401 Buttercup Creek Blvd #1006

I want to return Buttercup Creek

Removing the number is rather easy.

=FILTER(TRANSPOSE(SPLIT(<address>," ")),not(isnumber(TRANSPOSE(SPLIT(<address>," ")))))

How would I write an expression that then filters out a list of words I define? Suppose I build a table with the following values:

Or is there a way to do with a regex or something like that?

If I wasn't dealing with an array, I could search that table using this

=SUMPRODUCT(--ISNUMBER(SEARCH(<RefTable>,<cell with text>)))>0

Can you share your spreadsheet that shows the structure of your data and the desired output please? — ziganotschka, Apr 07 '20 at 07:35

score 0 · Accepted Answer · answered Apr 07 '20 at 10:26

0

try:

=ARRAYFORMULA(TRIM(IFNA(REGEXREPLACE(A2:A, "\d+|Blvd|#|Ave", ))))

answered Apr 07 '20 at 10:26

player0

1

That works well and is elegant in its simplicity. Is there a way to build that regular expression with a reference to a table versus hardcoding into the formula? – Jeffrey Ober Apr 07 '20 at 14:35
sure, if your exclusion values are in D column then: `=ARRAYFORMULA(TRIM(IFNA(REGEXREPLACE(A2:A, TEXTJOIN("|",1,D:D), ))))` – player0 Apr 07 '20 at 15:01
That is so cool! I modified that slightly to include the `\d+`. So the final was ```=ARRAYFORMULA(TRIM(IFNA(REGEXREPLACE(, TEXTJOIN("|",true,"\d+",), ))))``` – Jeffrey Ober Apr 07 '20 at 15:20

1 Answers1