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

--
Rick (MVP - Excel)


"Tom Hutchins" wrote in message
...
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

"Tom Hutchins" wrote:

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" wrote:

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

"Rick Rothstein" wrote:

Give this Conditional Formatting formula a try...

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

--
Rick (MVP - Excel)


"andreashermle" wrote in message
...
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

.