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