how to do the conditional formating for based on the limit of the characters in google sheet

Question

I want to have conditional formating based on the length of the chars of the cells in the column.

if the length>100 then mark it red and possibly in another cell, count how many reds or how many cells are length > 100.

How can i do it?

score 1 · Answer 1 · edited May 04 '20 at 10:01

1

The LEN function allows you to get the length of a string in a cell. For example if the cell A3 contained "hello", LEN(A3) would output 3.

To make the cell red, you can right-click on the cell and select conditional formatting. Then under the "Format cells if" select custom formula. You can then use =LEN(CELL_NUMBER)>100 as the formula and select red as the color to set it as.

You can also use the COUNTIF() to get the total count of cells with length greater than 100. Example usage is here: https://support.google.com/docs/answer/3093480?hl=en

edited May 04 '20 at 10:01

Rafa Guillermo

14,474
3
18
54

answered May 03 '20 at 10:32

pinkfluffyunicorn

91
5

thanks. =LEN(CELL_NUMBER)>100 can be applied on the range? – PythonDev May 03 '20 at 10:34
e.g. =LEN(I4:I)>100 – PythonDev May 03 '20 at 10:35
I dont believe LEN supports range but COUNTIF does. This should let you effectively sum the number of cells in a range where the LEN>100 – pinkfluffyunicorn May 03 '20 at 23:33

how to do the conditional formating for based on the limit of the characters in google sheet

1 Answers1