Count within Cell
 

I have a ticket number and I need a count within a cell.

I have 2000 lines of tickets and I need to make sure that the count within a
cell does not go over number.

Example:(Must be in this format)
LMN000001234567
KLM000000012345

Some entries of the ticket numbers sometimes are not inputted correctly.
I need highlights to show the wrong entries if there are more zeros in LMN
and KLM. Also if the digits in LMN goes over or under 7 digits and KLM goes
over or under 5 digits. They have to be in this format. If not they are
wrong.

I need LMN to have 5 zeros with 7 digits which means there is not a 0
infront of the 7 digits.
I need KLM to have 7 zeros with 5 digits which means there is not a 0
infront of the 7 digits.

Count within Cell
 

You could use data validation with a formula of

=AND(LEN(E1)=15,OR(AND(LEFT(E1,3)="LMN",MID(E1,4,5 )="00000",ISNUMBER(--RIGHT(E1,7)),LEN(RIGHT(E1,7))=LEN(SUBSTITUTE(RIGHT (E1,7),"0",""))),
AND(LEFT(E1,3)="KLM",MID(E1,4,7)="0000000",ISNUMBE R(--RIGHT(E1,5)),LEN(RIGHT(E1,5))=LEN(SUBSTITUTE(RIGHT (E1,5),"0","")))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"CYNTHIA" wrote in message
...
I have a ticket number and I need a count within a cell.

I have 2000 lines of tickets and I need to make sure that the count
within a
cell does not go over number.

Example:(Must be in this format)
LMN000001234567
KLM000000012345

Some entries of the ticket numbers sometimes are not inputted correctly.
I need highlights to show the wrong entries if there are more zeros in
LMN
and KLM. Also if the digits in LMN goes over or under 7 digits and KLM
goes
over or under 5 digits. They have to be in this format. If not they are
wrong.

I need LMN to have 5 zeros with 7 digits which means there is not a 0
infront of the 7 digits.
I need KLM to have 7 zeros with 5 digits which means there is not a 0
infront of the 7 digits.