Conditional formatting for character length

Hi,

I am working with a document that has a column that can only be 40 characters max when I upload it to the web. I would like to set it up so those cells highlight when it has more than 40 characters in it. I am constantly adding new items that come through and if it's greater than 40 characters, I'll need to manually revise them. So I'm not looking for something that will only allow 40 or cuts off after 40. I just want to be "notified" that it's too long.

As far as I can tell, conditional formatting is only based on the value of the cell - please give me any insight to this.

Thanks!

Use =LEN(A1) > 40 as the formula for conditional formatting.

Thank you! How do I get it to apply to every cell in the column? There are 500+ cells and when I try to select them all and enter in the formula, it's not working. It only works for me when I do it cell by cell - and I'm sure there's an easier way!

Thanks!

Lets say you want to apply it to column C from C3 to C533. Highight from C3 to C533 and go to Format >Conditional Format and enter =LEN(C3)>40. That should do it.

ChemistB

Select the range of cells to include the format, and in the formula insert the cell reference of the topmost, leftmost cell, without any dollar signs.

If you're using R1C1 references (but why?), then the formula should be =LEN(RC) > 40

Tools > Options > General, uncheck R1C1 reference style.

That worked - thank you!!

The spreadsheet is a template that I received from the company for uploading. It was designed that way - I'm guessing because it has to be that way to upload correctly??? I'm not even sure.

Thanks for the help on this - this will be so much easier going forward!!

You're very welcome.

Originally Posted by shg

If you're using R1C1 references (but why?), then the formula should be =LEN(RC) > 40

Tools > Options > General, uncheck R1C1 reference style.

I'd like to thank you for this answer, now 15 years in the future. I'm losing my mind trying to figure out why I am not getting this, and this gem of a comment is floating around out there.

Thanks MS, for this goofy thing. Thanks shg, for the answer. And I use R1C1 because everything I do is in macros spread across thousands of duplicate sheets (so no named ranges for this guy).