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

Give this Conditional Formatting formula a try...

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

--
Rick (MVP - Excel)


"andreashermle" <(E-Mail Removed)> wrote in message
news:513ab1ce-ea57-4550-83c0-(E-Mail Removed)...
> 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

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

--
MRT

"Rick Rothstein" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> Give this Conditional Formatting formula a try...
>
> =AND(A1<>"",OR(NOT(ISNUMBER(--SUBSTITUTE(A1,"-",""))),TEXT(SUBSTITUTE(A1,
> "-", ""), "##-###-##-##") <> A1))
>
> --
> Rick (MVP - Excel)
>
>
> "andreashermle" <(E-Mail Removed)> wrote in message
> news:513ab1ce-ea57-4550-83c0-(E-Mail Removed)...
>> 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
>

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

"andreashermle" wrote:

> 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
> .
>

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

--
Rick (MVP - Excel)


"MRT" <(E-Mail Removed)> wrote in message
news:e$(E-Mail Removed)...
> .....OR(LENB(A1)<>12,NOT..... :-)
>
> --
> MRT
>
> "Rick Rothstein" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Give this Conditional Formatting formula a try...
>>
>> =AND(A1<>"",OR(NOT(ISNUMBER(--SUBSTITUTE(A1,"-",""))),TEXT(SUBSTITUTE(A1,
>> "-", ""), "##-###-##-##") <> A1))
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>> "andreashermle" <(E-Mail Removed)> wrote in message
>> news:513ab1ce-ea57-4550-83c0-(E-Mail Removed)...
>>> 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
>>

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" <(E-Mail Removed)> wrote in message
> news:513ab1ce-ea57-4550-83c0-(E-Mail Removed)...
> > 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
>
> .
>

On 2 Nov., 16:00, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> Give this Conditional Formatting formula a try...
>
> =AND(A1<>"",OR(NOT(ISNUMBER(--SUBSTITUTE(A1,"-",""))),TEXT(SUBSTITUTE(A1,
> "-", ""), "##-###-##-##") <> A1))
>
> --
> Rick (MVP - Excel)
>
> "andreashermle" <andreas.her...@gmx.de> wrote in message
>
> news:513ab1ce-ea57-4550-83c0-(E-Mail Removed)...
>
>
>
> > 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- Zitierten Text ausblenden -
>
> - 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

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" <(E-Mail Removed)> wrote in message
> > news:513ab1ce-ea57-4550-83c0-(E-Mail Removed)...
> > > 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
> >
> > .
> >

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" <(E-Mail Removed)> wrote in message
> > > news:513ab1ce-ea57-4550-83c0-(E-Mail Removed)...
> > > > 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
> > >
> > > .
> > >

On 2 Nov., 16:46, Tom Hutchins <TomHutch...@discussions.microsoft.com>
wrote:
> 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
>
>
>
> "andreashermle" wrote:
> > 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
> > .- Zitierten Text ausblenden -
>
> - 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