3

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))
|improve this question
  • IMHO, the question is not clear because a formula isn't applied to a value, it uses values as arguments, so the question should be something like "how to get the numeric part of a cell value?" but I'm not sure if that is what the OP is looking for. – Rubén Jun 11 '16 at 20:08
  • @Rubén, thanks, I've tried to clarify the question. – texnic Jun 11 '16 at 21:41
  • The question is not clear yet, but looks that you have two situations. By one side, you should learn about arrays and array formulas in Google Sheets, by the other side looks that you should learn about custom formulas for conditional formatting. – Rubén Jun 12 '16 at 0:34
  • See Using arrays in Google Sheets - Google Docs Editors Help – Rubén Jun 12 '16 at 0:39
  • See Use conditional formatting rules in Google Sheets - Google Docs editors Help – Rubén Jun 12 '16 at 0:40
3

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

|improve this answer
  • 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 Jun 12 '16 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 – Aurielle Perlmann Jun 12 '16 at 8:37
  • See webapps.stackexchange.com/a/94711/88163 for a shorter formula. – Rubén Jun 12 '16 at 13:41
0

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.

|improve this answer
0

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

|improve this answer
  • 3
    you can do an array if you wrap it in arrayformula – Aurielle Perlmann Jun 12 '16 at 8:38
  • @AuriellePerlmann Thank you - I get to learn something new :) – Darius Jun 12 '16 at 11:46
0

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.

|improve this answer
-1

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.

|improve this answer
  • 2
    Would love to know the reason for downvote... – texnic Jun 15 '16 at 19:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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