See if the following is headed in the right direction, we can then attempt to make some refinements if possible.
Columns D and E respectively truncate the data at the first ";" and "," using: =IFERROR(LEFT(C2,SEARCH(";",C2)-1),"") and =IFERROR(LEFT(C2,SEARCH(",",C2)-1),"")
Column F displays the resulting (shorter) string using:
Formula:
=IF(AND(E2="",D2<>""),D2,IF(AND(E2<>"",D2=""),E2,IF(AND(E2<>"",D2<>"",LEN(E2)>LEN(D2)),D2,IF(AND(E2<>"",D2<>"",LEN(E2)<=LEN(D2)),E2,""))))
Column G displays strings that don't have either of the punctuations using: =IF(AND(D2="",E2=""),C2,"")
Column H displays the final list of strings using: =F2&G2
Column I displays the match (if any) using: =MATCH(H2,'ref data a sheet 1'!A$2:A$1000,0)+1
Bookmarks