In a Google Spreadsheet, I have a column with values like T1, T2, T13 (i.e. all values starting with the same text prefix). I would like to use a formula on the numerical part of the values of these cells (for conditional formatting). Can I somehow apply it only to the numeric part of the value, i.e. 1, 2, 13? I would like to change the background colour for the cell containing the maximum numeric part of the value?

I know how to extract the numeric part. E.g., if the T-values are in column A, cell B3 can contain this equation:

=if(len(A3)>1,value(right(A3, len(A3)-1)),0)

However I fail to apply any further formula to this. E.g. this doesn't work:

=max(if(len(A2:A)>1,value(right(A2:A, len(A2:A)-1)),0))

5 Answers 5


This formula looks really complicated but really its just a long nested arrayformula:


You want to use conditional formatting with a custom function :

You can see the image below but to explain a little, first what I did was remove the Letter portion using SUBSTITUTE leaving you the number, but then we had to format it as a digit using VALUE to get the max, following that I had to convert it back to a text string to use MATCH, and then INDEX to point to the cell that should be highlighted.

enter image description here

I am admittedly horrible at explaining how to do something once Ive figured out how to do it - but here is an attempt to break down the pieces in the formula:

enter image description here

  • Thanks Aurielle, your formula works, but I would really appreciate a little more detailed explanation for the educative purpose :) I've never used arrayformula, index, match and #. Could you explain why you need them here? And if you break the formula into several lines, it may become more clear what you're doing.
    – texnic
    Commented Jun 12, 2016 at 7:42
  • @texnic though explaining the parts is definitely not my strong suit - i added an image with the formula pieces and the results in attempt to help explain a little more Commented Jun 12, 2016 at 8:37
  • See webapps.stackexchange.com/a/94711/88163 for a shorter formula. Commented Jun 12, 2016 at 13:41

Try using this:


And then add your formula as needed.

To understand the formula:

RIGHT returns text from the end of a string, as marked as the "length".

I used LEN (length) to return the length of a cell, and minus 1. Example: T1 will have a length of 2. RIGHT of T1,1 will return 1. Example: T245, will have a LEN of 4. So RIGHT of T1,3 will return 245.

But RIGHT will return the result as a TEXT, hence why the need of VALUE to read the text, and return the TEXT into numbers.

By then you should be able to put your formula into calculations.

If you prefer to keep it as text, just remove the VALUE section.


If I read your formula correctly, you wanted the Max Value of all the values in column A but since A has T1, T2, T3 ... T9999 - you need to grab the numeric value of column A, and get the max of it.

It does looks like the OP is trying to re-apply existing working formula and extending it, but unfortunately due to the formula limitation (not even MS Excel can do it) it won't work.

=if(len(A3)>1,value(right(A3, len(A3)-1)),0)

Returns the value of A3's content, stripping the left most character, and returning it as a number (not string), or return 0 if it is empty.

If you want to get the MAX of all of those, what you need to do is unfortunately use up 1 whole column (maybe column B) with all the first formula (to take the numeric value out of A), and then get the MAX of that which is simply:


in Column C or other cell as required.

If this is not what is required, please clarify further.

Notes: LEN does not take an array (so you can't LEN(A2:A20)).

  • 3
    you can do an array if you wrap it in arrayformula Commented Jun 12, 2016 at 8:38
  • @AuriellePerlmann Thank you - I get to learn something new :)
    – Darius
    Commented Jun 12, 2016 at 11:46

An alternative to Aurielle Perlmann's formula


Right() is used to get the number from the cells as all values have a prefix of length 1. FILTER is used to avoid to have to update the formulas when more values be added.


Following hints in the answer by Aurielle, I've been able to produce a shorter formula:

=(A2:A)=text(max(arrayformula(value(substitute(A2:A,"T","")))), "T#")

It assumes that the first row is occupied by table caption.

Break down:

substitute(text,"T","") removes letter T from a cell text text.

value() converts the text result of substitute to a number.

arrayformula() applies value(substitute()) to each value from A2, A3, etc. range. The result is a numeric array.

max() returns the maximum value of that array.

text(number, "T#") converts the found maximum value to a string prefixed with letter T. T# is the format string, meaning "letter T, then number".

Finally, A2:A=... compares the values from A2, A3, etc. to the formed string. For the cells matching T+maximum value, the comparison will return TRUE, and conditional formatting will be applied.

  • 2
    Would love to know the reason for downvote...
    – texnic
    Commented Jun 15, 2016 at 19:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.