conditional formatting for wrongly entered 9 digit numbers
 

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

conditional formatting for wrongly entered 9 digit numbers
 

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

conditional formatting for wrongly entered 9 digit numbers
 

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

--
MRT

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

conditional formatting for wrongly entered 9 digit numbers
 

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
.

conditional formatting for wrongly entered 9 digit numbers
 

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

--
Rick (MVP - Excel)


"MRT" wrote in message
...
.....OR(LENB(A1)<12,NOT..... :-)

--
MRT

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

conditional formatting for wrongly entered 9 digit numbers
 

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

.

conditional formatting for wrongly entered 9 digit numbers
 

On 2 Nov., 16:00, "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- 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

conditional formatting for wrongly entered 9 digit numbers
 

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

.

conditional formatting for wrongly entered 9 digit numbers
 

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

.

conditional formatting for wrongly entered 9 digit numbers
 

On 2 Nov., 16:46, Tom Hutchins
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

conditional formatting for wrongly entered 9 digit numbers
 

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

.

conditional formatting for wrongly entered 9 digit numbers
 

On 2 Nov., 20:32, Tom Hutchins
wrote:
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

.- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Hi Tom,

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

conditional formatting for wrongly entered 9 digit numbers
 

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

"Rick Rothstein" wrote in message ...
Won't that highlight all the blank cells in the conditionally formatted
range?

--
Rick (MVP - Excel)


"MRT" wrote in message
...
.....OR(LENB(A1)<12,NOT..... :-)

--
MRT

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

conditional formatting for wrongly entered 9 digit numbers
 

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


"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"MRT" wrote in message
...
.....OR(LENB(A1)<12,NOT..... :-)

--
MRT

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