Conditional formatting based on length of the cell contents

[Cats]

Hi all

A customer is using Excel to define data which will later be mangled
into a database, but they have a habit of putting in strings that are
too long. I'd like to be able to format a range of cells so that if
(e.g.) the length of the text is greater than 8 it is coloured red (or
whatever). So, 'abcdefgh' would be OK and normal but 'abcdefghi'
wouldn't be and would be highlighted in some way.

The conditional formatting help wasn't helpful.

This is Excel 2007 / Excel 2010.

TIA

 

[Steve Dunn]

I would suggest you use Data Validation rather than Conditional Formatting
for this.

However, if you prefer, in Conditional Formatting, you can use a formula to
decide what to format when the formula returns TRUE.

For instance, if you wanted A1 to be formatted red when it's length was
greater than 8, the formula would simply be:

=LEN(A1)>8

To format a range of cells in this way, select the cells, and make the
formula refer to the active cell (the selected cell which is highlighted
white rather than blue).

HTH
Steve D.

 

[Gord Dibben]

Steve has a couple of good suggestions.

Would you like to take it step further and use event code to automatically
truncate the cell entry at 8 characters or less?


Gord Dibben MS Excel MVP

 

[Cats]

Steve has a couple of good suggestions.

Would you like to take it step further and use event code to automatically
truncate the cell entry at 8 characters or less?

Thanks, I feel that for what they are doing it's better if it simply
changes colour when they go beyond 8 (or whatever) characters - they
can see all of what they wanted to put which should make it easier to
work out how to truncate it without losing the meaning.

The first reply answered the question, wasn't sure about using 'data
validation' - wasn't sure what that meant.

 

[Steve Dunn]

Data Validation allows you to restrict what is entered in a cell, and/or
pop-up a message informing the user to re-try. You can even have a message
appear (similar to a comment), when the cell is selected.

If you are using Excel 2007 - Data Validation is under the Data tab on the
ribbon, unfortunately I can't remember where it is under 2003 and earlier.

Allow: Text Length

Data: less than or equal to

Maximum: 8

Take a look at the Input Message and Error Alert tabs too.

HTH
Steve D.

Steve has a couple of good suggestions.

Would you like to take it step further and use event code to automatically
truncate the cell entry at 8 characters or less?

Thanks, I feel that for what they are doing it's better if it simply
changes colour when they go beyond 8 (or whatever) characters - they
can see all of what they wanted to put which should make it easier to
work out how to truncate it without losing the meaning.

The first reply answered the question, wasn't sure about using 'data
validation' - wasn't sure what that meant.

 

[Gord Dibben]

OK

Just use CF for that.

=LEN(A1)>8


Gord

Thanks, I feel that for what they are doing it's better if it simply
changes colour when they go beyond 8 (or whatever) characters - they
can see all of what they wanted to put which should make it easier to
work out how to truncate it without losing the meaning.

The first reply answered the question, wasn't sure about using 'data
validation' - wasn't sure what that meant.