As part of a larger INDEX/MATCH formula, I am trying to extract text in a string after the last instances of commas or equal signs (whichever comes later in the string). I have so far been attempting to use the AGGREGATE function as it can handle errors for cells where one of these don't appear and evaluate the returned FIND values together, rather than using a series of nested arguments. When I use helper cells with
=FIND("@",SUBSTITUTE(D373,",","@",LEN(D373)-LEN(SUBSTITUTE(D373,",",""))),1)
and then AGGREGATE in a different cell, it works perfectly. However, why I try to nest the formulas to get rid of the helper cells using
=AGGREGATE(4,6,(FIND("@",SUBSTITUTE(D373,",","@",LEN(D373)-LEN(SUBSTITUTE(D373,",",""))),1)),(FIND("@",SUBSTITUTE(D373,"=","@",LEN(D373)-LEN(SUBSTITUTE(D373,"=",""))),1)))
I get a #VALUE error, apparently because AGGREGATE can no longer ignore the error returned by one of the FIND functions. Is there a way I can fix this or should I try another function altogether?
=FIND("@",SUBSTITUTE(D373,",","@",LEN(D373)-LEN(SUBSTITUTE(D373,",",""))),1)
and then AGGREGATE in a different cell, it works perfectly. However, why I try to nest the formulas to get rid of the helper cells using
=AGGREGATE(4,6,(FIND("@",SUBSTITUTE(D373,",","@",LEN(D373)-LEN(SUBSTITUTE(D373,",",""))),1)),(FIND("@",SUBSTITUTE(D373,"=","@",LEN(D373)-LEN(SUBSTITUTE(D373,"=",""))),1)))
I get a #VALUE error, apparently because AGGREGATE can no longer ignore the error returned by one of the FIND functions. Is there a way I can fix this or should I try another function altogether?