Google-sheets – Apply formula to a numeric part of cell value

google sheets

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

Best Answer

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.

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

Related Topic