Conditional Formatting IF character count 60

Microsoft Office excel 2007 home & stude...
March 4, 2011 at 06:33:48
Specs: Windows XP
Hello all, I'm hoping I can get your help again! I'm using Excel 2007.

I have a spreadsheet with a list of almost 10,000 Course names. I put data validation to limit the # of characters, but since the names are already there it doesn't show anything. I'd rather not add columns to count the characters, I'd love to have conditional formatting that turns the cell background RED if the # of characters in that cell are greater than 60.

I haven't been able to find any other strings on this site that combine conditional formatting with an IF statement and character counting formula (I believe it's LEN).

I am unsure as to how to use a formula in conditional formatting to count characters in a cell. I put in =IF(LEN(D2)>60,true) but it didn't work. And how does it count the characters in D3, D4 etc?

Help!! Thank you!


See More: Conditional Formatting IF character count 60

Report •

#1
March 4, 2011 at 07:20:06
The =LEN() functions will work, try this:

1) Select your range of cells IE: D1 thru D50

2) On the ribbon click Conditional Formatting

3) Click on New Rules, it’s near the bottom of the dialog box.

4) Click Use Formula to determine which cells to format.

5) Enter the formula: =IF(LEN(D1)>60,TRUE,FALSE)

6) Click on the Format button

7) Select the Fill Tab

8) Select a pretty color

9) Click OK

10) Click OK

Now any cell that has a length greater than 60 characters it will turn color.

MIKE

http://www.skeptic.com/


Report •

#2
March 4, 2011 at 07:35:49
Mike,

Unfortunately this didn't work. Two of the cells that changed color were one after another, the first one had 21 characters in it, and it turned... it shouldn't have!! the other had 63 characters in it and it DID turn (yay) but an identical name in the cell below it (also 63) didn't turn ... it should have.

So this isn't working. Any other ideas??


Report •

#3
March 4, 2011 at 09:21:58
Make sure that there are no stray SPACE characters around the names.

If you have <space>ABCD in a cell that will be 5 characters.
If you have ABCD<space> in a cell that will be 5 characters

Use the =TRIM() function to remove any unwanted Spaces.

Open up a new column next to your Names Column
then do a =TRIM(D1) and drag it down as many rows as needed.

Then select the range of cells that you just entered the formula into and
Copy,
Paste Special,
Values

This will retain the actual name minus any space characters.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
March 4, 2011 at 09:29:10
Mike,

Ok I think I know where you're going with this, but that's not what I'm looking for.

I have almost 10,000 names of programs for example "B Arts" which is 6 characters long, and "D Hospitality and Tourism Management- Intern Travel and Tourism" which is 63 characters long. What I am trying to do is leave the names as they are, and have the conditional formatting pull out any cells that the characters exceed 60 by turning the background red so we can do in and adjust the name manually.


Report •

#5
March 4, 2011 at 09:30:49
If your Names are being imported from another program,
and your still having problems after using the =TRIM() function.

Try using the =CLEAN() function.
while =TRIM() removes only the Space Character,
the =CLEAN() function will remove other Non-printable Characters from your text.

If you have <non-printable>ABCD in a cell that will be 5 characters.
If you have ABCD<non-printable> in a cell that will be 5 characters

Use the =CLEAN() function to remove any non-printable characters.

Open up a new column next to your Names Column
then do a =CLEAN(D1) and drag it down as many rows as needed.

Then select the range of cells that you just entered the formula into and
Copy,
Paste Special,
Values

This will retain the actual name minus any non-printable characters.

MIKE

http://www.skeptic.com/


Report •

#6
March 4, 2011 at 09:36:30
=CLEAN() function:
This function removes any nonprintable characters from text.
These nonprinting characters are often found in data which has been imported
from other systems such as database imports from mainframes.

=TRIM() function:
This function removes unwanted spaces from a piece of text.
The spaces before and after the text will be removed completely.
Multiple spaces within the text will be trimmed to a single space

The integrity of your list is not impacted.

The string B Arts will remain B Arts with 6 characters.
The string D Hospitality and Tourism Management- Intern Travel and Tourism will remain with 63 characters.

MIKE

http://www.skeptic.com/


Report •

#7
March 4, 2011 at 10:06:19
Mike,

Ok now I'm totally confused. I'm not sure where you got the idea that my data is being imported, it's not. It's already there. I don't want to remove the blank spaces, I need the names to stay exactly as they are. I realize that the LEN function counts every character including spaces, that's what I want it to do.

I need to create a conditional formatting on my existing list of 10,000 course names (as they are, with spaces) and have it highlight the cell in a red background if the total count of all characters (including spaces, hyphens etc) is greater than 60.


Report •

#8
March 4, 2011 at 11:34:13
The integrity of your list is not impacted.

The string B Arts will remain B Arts with 6 characters.
The string D Hospitality and Tourism Management- Intern Travel and Tourism will remain with 63 characters.

If the =LEN() function is telling you that a cell has 10 characters in it, and you can only see nine,
then there is either a SPACE character or a NON-PRINTABLE character somewhere in the cell.
To get rid of those unwanted characters you use the =TRIM() and/or =CLEAN() functions on your data.

Try using just the =LEN() function on your list of names.
Open up a new column next to your name column and put in the formula =LEN(D1)
and drag it down. Make sure to include some of your troublesome entries and see what the =LEN() function returns.

The formula I supplied worked on my test sheet.

MIKE

http://www.skeptic.com/


Report •

Ask Question