Apply formula to a numeric part of cell value

Question

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))

Answer 1

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

=IF(A:A=INDEX(A:A,MATCH(TEXT(MAX(ARRAYFORMULA(if(istext(A:A),VALUE(SUBSTITUTE(A:A,LEFT(A:A,1),"")),))),"#"),ARRAYFORMULA(SUBSTITUTE(A:A,LEFT(A:A,1),"")),0)),TRUE,FALSE)

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.

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:

Answer 2

Try using this:

=VALUE(RIGHT(A4,LEN(A4)-1))

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.

Answer 3

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:

=MAX(B2:BX)

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)).

Answer 4

An alternative to Aurielle Perlmann's formula

=Value(Right(A1,Len(A1)-1))
 =
 MAX(
   FILTER(
     Value(Right(A:A,Len(A:A)-1)),
     ISNUMBER(Value(Right(A:A,Len(A:A)-1))
   )
 )

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.

Answer 5

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.