Home » Questions » Computers [ Ask a new question ]

How do I format each cell in column FOO in Excel if the len() of the cell value is longer then BAR?

How do I format each cell in column FOO in Excel if the len() of the cell value is longer then BAR?

If this is the wrong place to ask this let me know.

Asked by: Guest | Views: 63
Total answers/comments: 1
Guest [Entry]

"If you need to generate the formatting programmatically for a large number of cells, you can do it with a macro (VBA), but if you're just looking for a quick and easy solution, you can drop the $'s from your original formula and copy and paste. In other words:

=LEN(A2)>$AB$11

Applies to A2, but if you copy and paste that into, say, B3, from B2 (that is to say, one cell down from the original), it will update itself to be this:

=LEN(A3)>$AB$11

You can paste across a large range of cells and it will update all of them. References without dollar signs are relative references, and will be updated in a copy/paste; references with dollar signs are absolute. I'm presuming, by the way, that AB11 holds a certain parameter you want to compare all cells to. If you're comparing them all to the single value, leave the dollar signs in that reference. If you're comparing a column to another column, relative references can do that for you too like this:

=LEN(A2)>$AB11"