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 ...
Thread: conditional formatting for wrongly entered 9 digit numbers in microsoft.public.excel.programming  |
|||||||||||||
|
Started 1 month, 3 weeks ago by 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....
|
|
||||||||||||
|
|||||||||||||
Other posts in this thread:
Tom Hutchins replied 1 month, 3 weeks ago
Size: 2,114 bytes
Customize: 
Rick Rothstein replied 1 month, 3 weeks ago
Give this Conditional Formatting formula a try...
=AND(A1<>"",OR(NOT(ISNUMBER(--SUBSTITUTE(A1,"-", ""))),TEXT(SUBSTITUTE(A1,
"-", ""), "##-###-##-##") <> A1))
--
Rick (MVP - Excel)
"andreashermle" <andreas.hermle@gmx.de> wrote in message
news:513ab1ce-ea57-4550-83c0-c69f2b0b67d7@o10g20 00yqa.googlegroups....
Size: 1,463 bytes
Customize:
MrT replied 1 month, 3 weeks ago
......OR(LENB(A1)<>12,NOT..... :-)
--
MRT
"Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message news:O9Of708WKHA.4780@TK2MSFTNGP05.phx.gbl...
> Give this Conditional Formatting formula a try...
>
> =AND(A1<>"",OR(NOT(ISNUMBER(--SUBSTITUTE(A1,"-","" ))),TEXT(SUBSTITUTE(A1,
> "-", ""), ...
Size: 1,823 bytes
Customize:
Rick Rothstein replied 1 month, 3 weeks ago
Won't that highlight all the blank cells in the conditionally formatted
range?
--
Rick (MVP - Excel)
"MRT" <mrt.0623@gmail.com> wrote in message
news:e$GrEM9WKHA.1792@TK2MSFTNGP04.phx.gbl...
> .....OR(LENB(A1)<>12,NOT..... :-)
>
> --
> MRT
>
> "Rick Rothstein" <rick.newsNO.SPAM@NO....
Size: 2,273 bytes
Customize:
Tom Hutchins replied 1 month, 3 weeks ago
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(--S UBSTITUTE(A1,"-",""))),TEXT(SUBSTITUTE(A1, "-", ""), "##-###-##-##") <> A1))
Hutch
"Rick Rothstein" wrote:
> Give this ...
Size: 2,082 bytes
Customize:
Tom Hutchins replied 1 month, 3 weeks ago
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 ...
Size: 2,557 bytes
Customize:
Tom Hutchins replied 1 month, 3 weeks ago
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:...
Size: 3,282 bytes
Customize:
andreashermle replied 1 month, 3 weeks ago
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...@...
Size: 1,976 bytes
Customize:
andreashermle replied 1 month, 3 weeks ago
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(...
Size: 2,872 bytes
Customize:
Rick Rothstein replied 1 month, 3 weeks ago
Thanks for catching the "leading zero" problem... yes, changing the # signs
to zeroes will solve the problem.
--
Rick (MVP - Excel)
"Tom Hutchins" <TomHutchins@discussions.microsoft.com> wrote in message
news:DFE9CD0D-DE4F-4E66-AAD7-C3AFF0FF086B@micros oft.com...
> Using 0 instead of # in the TEXT function ...
Size: 4,096 bytes
Customize:
|
Top contributing authorsRelated threads on "Discussions in microsoft.public.excel.programming":Related threads on other sites: |
||||||||||||
Thread profile page for "conditional formatting for wrongly entered 9 digit numbers in
microsoft.public.excel.programming" on http://www.microsoft.com/communities/newsgroups....
This report page is a snippet summary view from a single thread "conditional formatting for wrongly entered 9 digit numbers in
microsoft.public.excel.programming", located on the Message Board at http://www.microsoft.com/communities/newsgroups....
This thread profile page shows the thread statistics for: Total Authors, Total Thread Posts, and Thread Activity
Home |
About Us |
Submit Your Site |
Update Your Site |
Get Search For Your Site |
Partners |
Contact |
Search Plugin |
Blog |
 |
Last Searches | Top Searches |
Designated brands and trademarks are property of their respective owners.
Use of this website constitutes acceptance of BoardReader's User Agreement and Privacy Policy.