CODEWITHSUNDEEP
excelstringrangefind-occurrences
Miaka3
Miaka3

Reputation: 405

MS Excel: Find First Occurrence of Specific Text within a String Using Column Range List

Using a MS Excel formula that finds and extracts the very first (1st) instance/occurrence of a specific word found within a Column range of Keywords.

In Cell A2 I have a sub-string of text containing the following:

Karen was born in Akron, OH and attended college in Mobile, AL before moving to Atlanta, GA soon after her parents retired and now lives in Raleigh, NC.

Column B2:B10 (List of City & State keywords in a column list) 

Tulsa, OK  
New York, NY  
Boise, ID  
Atlanta, GA   
Baltimore, MD  
Raleigh, NC  
Pittsburgh, PA  
Akron, OH  
Mobile, AL

I've attempted to use the following formula's but without success.

=IFERROR(INDEX($B$2:$B$10,MATCH(TRUE,COUNTIF(A2,"*"&$B$2:$B$10&"*")>0,0)),"")

-AND- 

=IFERROR(INDEX($B$2:$B$10,MATCH(TRUE,ISNUMBER(SEARCH"*"&$B$2:$B$10&"*",A2)),0)),"")

Both Results Returned: Mobile, AL

Expected Results:

Correct Results = Akron, OH

Note: Although there are four possible matching results, instead it matches and retrieves/extracts Akron, OH, as it searches and finds the keyword position from Right to Left.

Upvotes: 1

Views: 4861

Answers (1)

user10829321
user10829321

Reputation:

Both Results Returned: Mobile, AL

Both of your attempted array formulas actually returned Atlanta, GA for me.

finds the keyword position from Right to Left.

Do you mean Left to Right ?

This is actually a reasonably common problem. You need to find the MIN of all matching SEARCH positions of the substrings within the string and then use that to find the index of the substrings within the list.

=INDEX(B2:B10, AGGREGATE(15, 7, ROW(1:9)/(SEARCH(B2:B10, A2)=AGGREGATE(15, 7, SEARCH(B2:B10, A2), 1)), 1))

enter image description here

Upvotes: 1

Related Questions