Compare a list of strings against a list of keywords to return a partial match

Hi all,

I'm attempting to clean up some data where I've been given a list of full street addresses (i.e. street number and name), but only want to extract (or confirm a match of) the street name. For example:

93 - 97 Harrison Street

Needs to just be: Harrison Street

105 Koromiko Road

Needs to just be: Koromiko Road

Cracroft Drive

Perfect as is

7 St Helens Place

Needs to just be: St Helens Place

I have a list of 700 or so streets, so could do a compare to this list, but I'm not sure exactly how to perform a partial match search that returns the above. Any help would be much appreciated!

Hi benishiryo,

Sorry if I didn't make it clear what I was after. It is probably quite a difficult data set to work with (I have around 60000 addresses to convert), but now I'm wondering if it may be easiest if I were to
filter out streets that do contain a number and deal with them separately (i.e. with a separate formula) to streets that do not. I have added a few more examples in the attached file below. The example of
"State Highway 4" is unusual - there's only a couple of roads that have a number at the end, so I could manually adjust those if a formula can't be used to tackle this. I think a very challenging data set!

Hi benishiryo and Glenn Kennedy,

benishiryo - Many thanks for the new formula. I've put this in and aside from only a couple hundred or so streets (out of 60000), it has done an excellent job of cleaning up the data.

Glenn Kennedy - I've got a fair bit more data coming up to "scrub", so I will give this a go soon. It appears to be a very useful tool. Thank you!