Excel Conditional Formatting Issue

[bbccnc]

Hi. I'm using Office 365 and am trying to create conditional formatting (CF) on a cell (P3) that is formatted as text. Once successful, I will apply the CF to column P for all rows in my spreadsheet. If the cell is not blank and the length of the entry is not 5 or 9 characters, the cell should be filled with red color. I've tried a number of different CFs but in all cases the cell is being filled with red even if the value has 5 or 9 characters. Here are some of the CFs I've tried, with no success:

=AND(OR(LEN(P3)<>5,LEN(P3)<>9),P3<>"")

=AND(OR((LEN(P3)<>5),(LEN(P3)<>9)),P3<>"")

=AND(P3<>"",OR(LEN(P3)<>5,LEN(P3)<>9))

=AND(P3<>"",(OR(LEN(P3)<>5,LEN(P3)<>9)))

=AND(P3<>"",(OR((LEN(P3)<>5),(LEN(P3)<>9))))

Any ideas?

[#2]

Hello @bbccnc,

Try this:

=NOT(OR(LEN(P3)=5,LEN(P3)=9,LEN(P3)=0))

[#3]

@bbccnc 

What a fascinating challenge. Are you able to use DataValidation to put a limit of, say, 9 characters on cell P3; or, at the very least, a limit of some quite finite number, not to exceed something in the order of 15.

I ask because this is the formula I found to work: =AND(CHOOSE(LEN(P3),1,1,1,1,0,1,1,1,0),NOT(ISBLANK(P3)))

which doesn't altogether display in the dialog box below. I limited the range of choices in the CHOOSE function to 9. It could be extended, but not forever. As it's written, if somebody enters a longer text it turns white.

It operates by returning a value of "TRUE" as you'll see in cell R3 of the attached sample copy, under the conditions of a length other than 5 or 9, and not being blank. Otherwise it's false. But as noted, you might want to add some more "1" figures to the CHOOSE function if you're need to allow for longer text entries.

t's also possible I inverted your logic, but either way, a variation on this should work.

[#4]

@bbccnc 

As a comment

OR() returns TRUE if any of parameters is TRUE. In the formula like

=AND(OR(LEN(P3)<>5,LEN(P3)<>9),P3<>"")

text length can't be simultaneously 5 or 9, thus at least one of conditions is TRUE, thus OR always returns TRUE. With AND condition FALSE is returns if only P3 is blank. Thus we have TRUE for any non-empty value in P3.

If highlight cell which is not blank AND length is not 5 AND length is not 9 when

=AND(LEN(P3)<>5,LEN(P3)<>9,P3<>"")

[#5]

@bbccnc 

You got yourself three solutions. It was fun to work on. We've demonstrated the truth that is so often stated here, that in Excel there are multiple routes to the same destination.

In this case, I tip my hat to @PReagan, supplemented by @Sergei Baklan  for coming up with the cleaner, less obscure solution.. :)

[#6]

@mathetes 

Many thanks for the compliment. As you said, the beauty of excel is that there are often several answers to the same problem.

[#7]

@PReagan Thanks for your solution. It worked.

[#8]

@mathetes Hi and thank you for your response. Unfortunately I can't use data validation because my process is actually copying data from a flat file, pasting it into my Excel spreadsheet, then applying the conditional formatting versus users actually entering values directly into the spreadsheet. Another's response worked for me and I learned a few new things from the solution you provided.

[#9]

@Sergei Baklan Many thanks for your response. You are right about the "OR" evaluation...seems I had quite a senior moment there :)

[#10]

@bbccnc 

My pleasure!