Get corresponding text

[Excel Fox]

Try this

=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)

Regards,
Excel Fox

[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.

[Rick Rothstein]

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.

**** CORRECTION ****

While the quoted formula works, it does not duplicate the functionality of your formula exactly. Your formula adapts to a varying number of fields in A1 whereas my formula assumed 4 fields (and 4 data for those fields) was constant. The below formula, a little longer than my original formula, but still shorter than yours (and it still works in XL2003 as well), duplicates the functionality of your formula exactly (well, except for when C1 is blank)...

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

[Rick Rothstein]

**** CORRECTION ****

While the quoted formula works, it does not duplicate the functionality of your formula exactly. Your formula adapts to a varying number of fields in A1 whereas my formula assumed 4 fields (and 4 data for those fields) was constant. The below formula, a little longer than my original formula, but still shorter than yours (and it still works in XL2003 as well), duplicates the functionality of your formula exactly (well, except for when C1 is blank)...

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

**** CORRECTION #2 ****

Just noticed a flaw in both of our formulas... if the field name being searched for is embedded as text within an earlier field name, the data for that earlier field name will be found first. For example, if A1 contained this...

name,mortgage,age,Bob,Yes,26

both our formulas would see the letters "age" in mortgage and return Yes whether C1 contained age or mortgage. Here is my code corrected to remove this flaw...

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

Oh, and one other difference between our formulas... yours is case sensitive while mine is not.