| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Formatting according to the date the data is entered | Excel Discussion (Misc queries) | |||
| entering 16 digit numbers - last digit turns to zero | New Users to Excel | |||
| conditional format on first & last digit on 3 digit cell data | New Users to Excel | |||
| Formatting Numbers to 3 digit, | Excel Discussion (Misc queries) | |||
| 16 DIGIT NUMBERS IN CELL WITHOUT LAST DIGIT BEING A ZERO | Excel Worksheet Functions | |||
Linear Mode
