condittions max charactersor highlight

[richiwatts]

Hi

I have 2 columns of text
C-A is only allowed to have 30 characters
C-B is only allowed to have 50 characters

Is there any way I can highlight any cells that have gone over this limit and the highlighting will disapear as sson as I edit the text to try and make it below the amount?

CP

 

[Tom Urtis]

If they are "only allowed" for 30 or 50 characters, how did they go over the limit in the first place? Are you using Data Validation to monitor the text length?

 

[richiwatts]

Hi,

No, this is just want our client wants us to insure. I know that some of them are over but there are too many cells to check manually.

This is why I need to know what the formular is for Conditional formatting so I can highlight any cells that are over these amounts. I just do not know how to write the formular

CP

 

[SteveO59L]

Use the len() function in your conditional format

 

[Tom Urtis]

You can use Conditional Formatting. If for example your "30 max" column is A, then press Ctrl+Home to activate cell A1, click on the "A" header to select all of column A, and then from the worksheet menu click Format > Conditional Formatting. From the first drop-down select "Formula is" and in the field to the right enter the formula
=LEN(A1)>30

Click the Format button, Patterns tab, select a color from the palette, click OK, OK, and you are done with that column. Repeat the steps for the "max 50" column, if it is B, then the CF formula with cell B1 active and column B selected would be
=LEN(B1)>50

 

[richiwatts]

I did exactly what you said but it doesn't do anything. I have even reduced the number to >10 but still nothing

 

[Tom Urtis]

If you did it exactly as I said then it would be working. Something else is going on, maybe you had a different active cell selected than A1 with column A selected and the formula as entered did not match the address of the active cell. Or maybe you did not specify the formatting. Check your sheet again to make sure you selected each of the two proper ranges for the CF formulas being applied, and that you indeed clicked the Format button on the Conditional Formatting dialog to specify a cell shade color.

 

[richiwatts]

Just to see if I had done it correct, I marked a single cell (B24) opened the conditional formatting window.

It showed:

formular is: =LEN(B24)>30

Formatting = background red + bold

80% of the cells have over 30 characters

CP

 

[GlennUK]

Describe exactly what you did, including what you selected at what stage, and what cell was the active cell at the time and so on and so forth, and we'll try to spot what went wrong.

 

[richiwatts]

Hi,

Because I am using a Swedish version of Excel I had to use

=LÄNGD

instead of

=LEN

Problem solved thanks