假设原数据在A和B列，如图：
F2公式：
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(PHONETIC(B$2:B$100),",",""),"c",REPT(" ",LEN(SUBSTITUTE(PHONETIC(B$2:B$100),",","")))&"c"),LEN(SUBSTITUTE(PHONETIC(B$2:B$100),",",""))*ROW(A1)+1,LEN(SUBSTITUTE(PHONETIC(B$2:B$100),",",""))))
下拉F2填充柄，复制公式；
E2公式：
=INDEX(A$1:A$100,MAX(ISNUMBER(FIND(F2&",",B$1:B$100&","))*ROW($1:$100)))&""
数组公式，按组合键CTRL+SHIFT+回车完成公式，下拉E2填充柄，复制公式。