#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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Fluff

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,903
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,118,814
Messages
5,574,487
Members
412,597
Latest member
Timtec
Top