conditional formatting for wrongly entered 9 digit numbers

[andreashermle]

Dear Experts:

I need to enter 9 digit numbers in column A. These item numbers have
got the following make-up:
##-###-##-##

I now would like to assign conditional formatting to the whole column
A with the following condition: As soon as the entered number does not
match the above syntax, the cell is to be filled red.

Is that possible?

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

 

[Rick Rothstein]

Give this Conditional Formatting formula a try...

=AND(A1<>"",OR(NOT(ISNUMBER(--SUBSTITUTE(A1,"-",""))),TEXT(SUBSTITUTE(A1,
"-", ""), "##-###-##-##") <> A1))

 

[MRT]

......OR(LENB(A1)<>12,NOT.....

--
MRT

Give this Conditional Formatting formula a try...

=AND(A1<>"",OR(NOT(ISNUMBER(--SUBSTITUTE(A1,"-",""))),TEXT(SUBSTITUTE(A1,
"-", ""), "##-###-##-##") <> A1))

 

[Tom Hutchins]

One way...

Format column A with a custom format: ##-###-##-##
Select column A. Select Format >> Conditional Formatting. Change 'Cell Value
Is' to 'Formula Is' and enter this formula in the box next to it:

=(OR(LEN(A1)=0,ISNUMBER(A1)=TRUE))=FALSE

Click the Format button. On the Patterns tab, click red. Click OK to close
the Format dialog, then OK again to close the Conditional Formatting dialog.

These directions are based on Excel 2003. I don't have 2007 available at the
moment, but the steps are similar. You need to add a rule based on a formula
(the formula above).

With the above custom format and conditional formatting in place, your users
will enter the digits only - the custom format will put in the dashes.
Missing, extraneuous, or invalid characters will cause the conditional
formatting to kick in and highlight the field.

Hope this helps,

Hutch

 

[Rick Rothstein]

Won't that highlight all the blank cells in the conditionally formatted
range?

 

[Tom Hutchins]

Brilliant! Let me just add that if the codes might begin with zero, an
additional test should be added to Rick's conditional formatting formula:

=AND(A1<>"",LEFT(A1,1)<>"0",OR(NOT(ISNUMBER(--SUBSTITUTE(A1,"-",""))),TEXT(SUBSTITUTE(A1, "-", ""), "##-###-##-##") <> A1))

Hutch

 

[andreashermle]

Give this Conditional Formatting formula a try...

=AND(A1<>"",OR(NOT(ISNUMBER(--SUBSTITUTE(A1,"-",""))),TEXT(SUBSTITUTE(A1,
"-", ""), "##-###-##-##") <> A1))

--
Rick (MVP - Excel)











- Zitierten Text anzeigen -

Hi Rick,

great, what a code. It works as desired. Thank you for your superb /
professional help. I really appreciate it. Regards, Andreas

 

[Tom Hutchins]

That revision is wrong (would allow 0A-234-56-78 to pass), but both Risk's
formula & mine cause any code beginning with zero (such as 01-234-56-78) to
fail.

Hutch

 

[Tom Hutchins]

Using 0 instead of # in the TEXT function seems to handle leading zeros
without causing new problems:

=AND(A10<>"",OR(NOT(ISNUMBER(--SUBSTITUTE(A10,"-",""))),TEXT(SUBSTITUTE(A10,
"-", ""), "00-000-00-00") <> A10))

Sorry for all the posts. Doing too many things at once.

Hutch

 

[andreashermle]

One way...

Format column A with a custom format: ##-###-##-##
Select column A. Select Format >> Conditional Formatting. Change 'Cell Value
Is' to 'Formula  Is' and enter this formula in the box next to it:

=(OR(LEN(A1)=0,ISNUMBER(A1)=TRUE))=FALSE

Click the Format button. On the Patterns tab, click red. Click OK to close
the Format dialog, then OK again to close the Conditional Formatting dialog.

These directions are based on Excel 2003. I don't have 2007 available at the
moment, but the steps are similar. You need to add a rule based on a formula
(the formula above).

With the above custom format and conditional formatting in place, your users
will enter the digits only - the custom format will put in the dashes.
Missing, extraneuous, or invalid characters will cause the conditional
formatting to kick in and highlight the field.

Hope this helps,

Hutch










- Zitierten Text anzeigen -

Hi Tom,

I am afraid to tell you that your effort did not produce the desired
result. Anyhow, I really appreciate your taking the time to help me.
Thank you. Rick's formula works as desired. Any number or string other
than ##-###-##-## hightlights the cell red with Rick's formula.

Regards, Andreas

 

[Rick Rothstein]

Thanks for catching the "leading zero" problem... yes, changing the # signs
to zeroes will solve the problem.

 

[andreashermle]

Using 0 instead of # in the TEXT function seems to handle leading zeros
without causing new problems:

=AND(A10<>"",OR(NOT(ISNUMBER(--SUBSTITUTE(A10,"-",""))),TEXT(SUBSTITUTE(A10­,
"-", ""), "00-000-00-00") <> A10))

Sorry for all the posts. Doing too many things at once.

Hutch





- Zitierten Text anzeigen -

Hi Tom,

great. Thank you very much for this tip. Regards, Andreas

 

[MRT]

I cannot judge between
allow highlight all the blank cells
or
allow like"99999-999-99-99" without highlight

yes! best way is . do not allow both,
...... ,OR(MID(A1,3,1)<>"-",NOT( .....

 

[MRT]

I cannot judge between
allow highlight all the blank cells
or
allow like"99999-999-99-99" without highlight

yes! best way is . do not allow both,
...... ,OR(MID(A1,3,1)<>"-",NOT( .....