Example of my google sheet spreadsheet cells below
| 1 | AAA C1 | BBB C4 | AAA C7 |
A1: “AAA C1” B1: “BBB C4” C1: “AAA C7”
I would need to maximize the highest numbers after the letter “C” from all the strings that start with “AAA”. I have only reached to maximize all numbers after “C” by using the formula:
=Max(ARRAYFORMULA(VALUE(REGEXREPLACE(a1:c1,"[^[:digit:]]", ""))))
Nevertheless, with the above formula I have not been able to select only the strings that start with “AAA”. I have tried the function =maxifs, but it does not allow string functions such as MID being applied to the range of cells.
This is my first question here, I hope it is all clear and someone can help me with this problem.
Thanks!
Advertisement
Answer
Suggested Solution #1:
=Max(ARRAYFORMULA(VALUE(REGEXREPLACE(FILTER(A1:C1,ISNUMBER(SEARCH("AAA",A1:C1))),"[^[:digit:]]", ""))))
I left your original formula essentially as it was, since you already understand it as it was. All I did was replace your range with a FILTER of that range.
The FILTER alone — FILTER(A1:C1,ISNUMBER(SEARCH("AAA",A1:C1))) — keeps only those entries in the range where searching for “AAA” results in a position number (i.e., all matches for “AAA” as anything else would result in an error).
If the “AAA” must appear first in the string, you can use this version:
=Max(ARRAYFORMULA(VALUE(REGEXREPLACE(FILTER(A1:C1,SEARCH("AAA",A1:C1)=1),"[^[:digit:]]", ""))))
Suggested Solution #2:
=Max(ARRAYFORMULA(IFERROR(VALUE(REGEXEXTRACT(A1:C1,"^AAA.+(d+)$")),-9^9)))
This will find the max from REGEXEXTRACTs matching only the “AAA” cells. If something doesn’t match the “AAA” pattern, the IFERROR will return an incredibly high negative number (which will rule those out from ever being the MAX).