Quote Originally Posted by Rick Rothstein View Post
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)),"")