krjoshi,
Attached is a modified version of the workbook you posted.
In cell C2 is this formula:
=IFERROR(TRIM(MID(SUBSTITUTE(B2,",",REPT(" ",99)),SEARCH("crane",SUBSTITUTE(B2,",",REPT(" ",99)))-3,99)),"")
In cell D2 is this formula:
=IFERROR(TRIM(MID(SUBSTITUTE(B2,",",REPT(" ",99)),SEARCH("crane",SUBSTITUTE(B2,",",REPT(" ",99)),FIND(C2,SUBSTITUTE(B2,",",REPT(" ",99)))+99)-3,99)),"")
It looks like there are only 4 rows that contain the word "crane" twice. I confirmed using this formula:
=(LEN(B2)-LEN(SUBSTITUTE(B2,"CRANE","")))/LEN("CRANE")
Bookmarks