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:
[TABLE="width: 212"]
<tbody>[TR]
[TD]022006000015582 Bad[/TD]
[/TR]
[TR]
[TD]030020000157510 Bad[/TD]
[/TR]
[TR]
[TD]320220000093166 Good[/TD]
[/TR]
[TR]
[TD]000004001100000 Bad[/TD]
[/TR]
[TR]
[TD]000000000163132 Bad[/TD]
[/TR]
[TR]
[TD]120180000103126 Good[/TD]
[/TR]
[TR]
[TD]200010000119899 Good[/TD]
[/TR]
[TR]
[TD]A00100000033408 Good
[/TD]
[/TR]
[TR]
[TD]003401400086119 Bad
[/TD]
[/TR]
[TR]
[TD]120180000102297 Good
[/TD]
[/TR]
[TR]
[TD]34014 00000 73023 Bad
[/TD]
[/TR]
[TR]
[TD]200010000119899 Good
[/TD]
[/TR]
[TR]
[TD]040010000095304 Bad
[/TD]
[/TR]
[TR]
[TD]CONS/460190000049100 Bad
[/TD]
[/TR]
[TR]
[TD]000000000163089 Bad
[/TD]
[/TR]
[TR]
[TD]28012 0000106955 Bad
[/TD]
[/TR]
[TR]
[TD]Thank you![/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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")