Get corresponding text

[Rick Rothstein]

=LEFT(MID(A1,FIND("|",SUBSTITUTE(A1,",","|",LEN(LE FT(","&A1,FIND(C1,","&A1)-1))-LEN(SUBSTITUTE(LEFT(","&A1,FIND(C1,","&A1)-1),",",""))+(LEN(","&A1)-LEN(SUBSTITUTE(A1,",","")))*0.5-1))+1,255)&",",SEARCH(",",MID(A1,FIND("|",SUBSTITU TE(A1,",","|",LEN(LEFT(","&A1,FIND(C1,","&A1)-1))-LEN(SUBSTITUTE(LEFT(","&A1,FIND(C1,","&A1)-1),",",""))+(LEN(","&A1)-LEN(SUBSTITUTE(A1,",","")))*0.5-1))+1,255)&",")-1)

Your formula appears to work only in XL2007 and above (too many nested levels for XL2003 I presume). In any event, here is a shorter formula that appears to work correctly in XL2003 and above...

=IF(LEN(C1),TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",999)),(5+LEN(LEFT(A1,SEARCH(C1,A1)-1))-LEN(SUBSTITUTE(LEFT(A1,SEARCH(C1,A1)-1),",","")))*999-998,999)),"")

Note that I added a test for C1 being blank to suppress the value that otherwise gets displayed.