#VALUE error when using AGGREGATE to find the last of multiple last instances of characters

MESTRAAL

New Member
Joined
Oct 7, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,057
Office Version
  1. 365
Platform
  1. Windows
Do you have the LET function yet?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,057
Office Version
  1. 365
Platform
  1. Windows
Forget that, it doesn't make much difference.
How about
Excel Formula:
=FIND("@",SUBSTITUTE(SUBSTITUTE(D373,"=",","),",","@",LEN(D373)-LEN(SUBSTITUTE(SUBSTITUTE(D373,"=",","),",",""))))
 

Watch MrExcel Video

Forum statistics

Threads
1,119,085
Messages
5,576,027
Members
412,694
Latest member
Deaf1Too
Top