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:
- #
- Blvd
- Blvd.
- Boulevard
- ...
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
