Limiting Characters (Including Spaces) in a cell

Fusco

New Member
Joined
Mar 10, 2006
Messages
12
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

shajueasow

Well-known Member
Joined
Oct 7, 2004
Messages
1,926
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,413
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 10, 2006
Messages
12
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,413
Office Version
  1. 365
Platform
  1. Windows
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
AEAFAGAHAIAJAKALAMANAOAPAQARAS
1
2SOCKSMENSRALPH LAURENMENS RALPH LAUREN SOCKS
3SOCKSMENSXYZMENS XYZ SOCKS
4
Conditional Format
 

Forum statistics

Threads
1,136,310
Messages
5,675,002
Members
419,543
Latest member
Casp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top