Limiting Characters (Including Spaces) in a cell

[Fusco]

Hi All,

This may be an easy one for most, but I can't seem to figure it out. I would like if a cell contains more than 15 characters (Including spaces) then Excel turns the background red. I know it is conditional formatting, I just don't know how. Please help.

Thanks
Fusco

 

[shajueasow]

In Conditional formattig try using the following formula:
=LEN(A1)>15
Select form the drop down "Formula is" and in the second box enter the above formula and use Format button to change the pattern to Red color.
HTH

 

[Peter_SSs]

Fusco

From your description you want Conditional Formatting and that is what shajueasow has given you. However, your post subject 'Limiting Characters (Including Spaces) in a cell' indicates that Data Validation may also be of use to you. If the data is being entered directly then this would stop more than 15 characteres being entered in the first place.

You might want to consider
Data|Validation...|Settings|Allow: Text Length|Data: Less than or equal to|Maximum: 15|OK

 

[Fusco]

Thanks but the conditional formatting still does not work. I forgot to mention that I also have =AH2&" "&AR2&" "&AE2&"" as the formula within the cell I need turned red.

AH2 = MENS
AR2 = RALPH LAUREN
AE2 = SOCKS

So the results would be MENS RALPH LAUREN SOCKS in the destination cell. This is over 15 characters, so I would like if it turned red. Any Help? Thanks for the replies.

 

[Peter_SSs]

Thanks but the conditional formatting still does not work. I forgot to mention that I also have =AH2&" "&AR2&" "&AE2&"" as the formula within the cell I need turned red.

AH2 = MENS
AR2 = RALPH LAUREN
AE2 = SOCKS

So the results would be MENS RALPH LAUREN SOCKS in the destination cell. This is over 15 characters, so I would like if it turned red. Any Help? Thanks for the replies.

Unrelated to the Conditional Formatting problem, but why the "" at the end of your formula? This should do just as well:
=AH2&" "&AR2&" "&AE2

Perhaps you have not applied the Conditional Formatting correctly. It works for me. Here it is in detail and assumes the formula is in cell AS2. Adjust the Conditional Formatting formula if your formula is in a different cell.

1. Select the formula cell (AS2 in my case)
2. Format|Conditional Formatting...|Condition1|Use the drop-down to select 'Formula is'|in the right hand box type: =LEN(AS2)>15|Format...|Patterns|choose red|OK|OK
3. Use the Format Painter to apply to other cells in the column if necessary.

Here is the result for two rows.

Mr Excel.xls
	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS
1
2	SOCKS			MENS										RALPH LAUREN	MENS RALPH LAUREN SOCKS
3	SOCKS			MENS										XYZ	MENS XYZ SOCKS
4
Conditional Format

 

[Fusco]

Peter,

Thank you so much. It works flawlessly.

Jon