1

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.

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
Miaka3
  • 339
  • 1
  • 10
  • 27

1 Answers1

1

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

  • Thank you, this formula works. Are you able to provide me with the helper column formula found in C2 and downward (<- smallest left-to-right position). Also I used the substitute formula {=SUBSTITUTE(A2,D2,"")} in Cell A3, that removes Akron, OH from the initial text. Now, I would like to find the next word found after removing Akron, OH, from the string which in this case would result in Mobile, AL. – Miaka3 Dec 24 '18 at 14:32
  • The formula in C2 is simply `=IFERROR(SEARCH(B2, A$2), "")` filled down. To find the second match change `, 1)), 1))` to `, 2)), 1))`. This modifies the *k* argument of AGGREGATE from smallest to second smallest. –  Dec 24 '18 at 14:37
  • Very nice formula *(+1)* – Gary's Student Dec 24 '18 at 14:45