Quote Originally Posted by Rick Rothstein View Post
**** 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.