Search a cell for a value that exists in a list to pull a value in Excel

Question

In Excel, I have a list of strings that within the cells contain the name of a State. I then have a list of the States and I want to have a formula that is able to search the string for a State name and then give me the name of the State.

I've used nested =IF(COUNTIF(A1,"*Florida*")=1,"Florida"...) in the past for similar exercises but I don't want to create a version for all 50 States. Is there a way to do this combining some kind of INDEX MATCH?

Image below is a snippet of the kind of data. For the most part, the State name follows the year but not always and the suffix isn't always Invitational so there's no way to use those two to book-end the part of the string that contains the State.

Any help would be appreciated!

example data

Answer 1

Use this Formula

=IF(ISNUMBER(VALUE(LEFT(A1,1))),MID(A1,FIND(" ",A1)+1,
FIND("~", SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-(FIND(" ",A1)+1)),
LEFT(A1,FIND("~", SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

if you have office 365, and you don't want to use an extra helper column then this formula can be helpful

=IFERROR(IF(ISNUMBER(VALUE(LEFT(A1,1))),TEXTAFTER(TEXTBEFORE(A1," ",2,0,1,"")," ",1,0,1,""),TEXTBEFORE(A1," ",1,0,1,"")),"")

Just Added New formula so you can avoid using a new helper column

All you have to do is, "-" in the state names with space. like New-York As if you use New York, The result will only be New

Answer 2

Assuming you have the filter function, here's a clean short formula. Assume Column E:E has your list of states, and then just reference the cell...

=Textjoin("",true,FILTER(if(isnumber(Search(E:E,A2)),E:E,),E:E<>""))

Updated:

Stealing from JvDV this is probably a better version of what I tried to setup:

=FILTER(E:E,(isnumber(SEARCH(E:E,A2))*(E:E<>"")))

Answer 3

Possibly something along these line:

Formula in B1:

=FILTER(D$2:D$4,COUNTIF(A1,"*"&D$2:D$4&"*"))

Or:

=FILTER(D$2:D$4,ISNUMBER(FIND(D$2:D$4,A1)))