Formula = If cell does not contain 15 characters, then highlight orange

[westc4]

Hello,

I am sure this is an easy question, but I have not been able to get it to work for me. I have 5000+ rows that can contain both numbers, alpha and a mix of both. The column is formatted custom '000000000000000' (15). What I need is if the cell contains anything other than 15 (no spaces) characters highlight the cell to be orange (or any other color).

The first character could be alpha or number (never a zero), but the following 14 characters will always be a number.

Here is a sample set of data:

022006000015582 Bad

030020000157510 Bad

320220000093166 Good

000004001100000 Bad

000000000163132 Bad

120180000103126 Good

200010000119899 Good

A00100000033408 Good

003401400086119 Bad

120180000102297 Good

34014 00000 73023 Bad

200010000119899 Good

040010000095304 Bad

CONS/460190000049100 Bad

000000000163089 Bad

28012 0000106955 Bad

Thank you!

<tbody>
</tbody>

 

[Joe4]

Just use Conditional Formatting, with a formula like:

=LEN(SUBSTITUTE($A1," ",""))=15

 

[Rick Rothstein]

Just use Conditional Formatting, with a formula like:

=LEN(SUBSTITUTE($A1," ",""))=15

The OP shows some cells with 15 digits and some interspersed spaces which would pass your test but which he has marked as bad. I think we need to know from the OP whether his good spaces are 15 characters long with no spaces.

 

[westc4]

Thank you! I knew it should not be as difficult as I was making it.

I did change the formula a little to

=LEN($A1)<>15

This will highlight the cells that have more or less than the 15 characters and does not ignore the spaces.

 

[Joe4]

Sorry, I actually meant to say "<>15" instead of "=15", i.e.

=LEN(SUBSTITUTE($A1," ",""))<>15

Originally, I just had the LEN without the SUBSTITUTION part, but I thought that you said it needs to be 15 characters, not counting the spaces?
So are you telling me that if you have the number 1, with 13 spaces, and then another number 1, that would be OK?
The code with the SUBSTITUTE would catch and highlight that, but the code without it wouldn't.

Unless you are saying that you know for sure that there will ALWAYS be 15 non-space characters, and then you are just looking for extra spaces?
If that is the case, and you want to look for that and any values starting with a zero, then use:

=OR(LEN($A1)=15,LEFT($A1,1)="0")