Results 1 to 9 of 9

20061207, 10:44 #1
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Condition format wrong tel number (Excel 2003)
Hi all,
Looooong time no see!! Nice to log in again!
I need help to check for wrong entered tel numbers. i want the tel number to shade in red if it has the wrong structure. Please open the attached WB and help me correct the formula I have worked on. I do have it working, but if you look at the tel number in row 11, it should be in red as there is a space in there. I need this corrected please, as well as any other potentioal problem that violates the valid structure as in row 3.
TXRegards,
Rudi

20061207, 10:56 #2
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Condition format wrong tel number (Excel 2003)
Hi,
I think I corrected it by adding another logical into the AND function. It is now: =NOT(AND(LEN(A3)=11,LEN(LEFT(A3,3))=3,MID(A3,4,1)= " ",LEN(RIGHT(A3,7)=7))).
This takes care of the space that was incorrect in the first post.
BUT,
Is there a more simple condition to use than this multinested expression??
CheersRegards,
Rudi

20061207, 11:10 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Condition format wrong tel number (Excel 2003)
Conditions such as
LEN(LEFT(A3,3))=3
and
LEN(RIGHT(A3,7)=7
are meaningless  unless the string is too short, the length of the first 3 characters is always 3!
Try this:
=IF(ISERROR(FIND(" ",A3)),TRUE,IF(FIND(" ",A3)=4,IF(ISERROR(FIND(" ",A3,FIND(" ",A3)+1)),LEN(MID(A3,FIND(" ",A3)+1,100))<>7,TRUE),TRUE))

20061207, 11:26 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Condition format wrong tel number (Excel 2003)
I don't think there's a really simple solution. You could hide the nasty details in a userdefined function, for example:
Function WrongPhone(strPhone As String) As Boolean
Dim i As Integer
Dim c As Integer
If Not Len(strPhone) = 11 Then
WrongPhone = True
Else
For i = 1 To Len(strPhone)
c = Asc(Mid(strPhone, i, 1))
If i = 4 Then
If Not c = 32 Then
WrongPhone = True
Exit Function
End If
Else
If c < 48 Or c > 57 Then
WrongPhone = True
Exit Function
End If
End If
Next i
End If
End Function
(This also checks that no characters other than spaces and digits are used). The formula for conditional formatting becomes simply
=WrongPhone(A3)

20061207, 12:03 #5
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,444
 Thanks
 166
 Thanked 651 Times in 619 Posts
Re: Condition format wrong tel number (Excel 2003)
Hi Hans
..how about this function:
Function WrongPhone(strPhone As String) As Boolean
rightPhone = strPhone Like "### ######"
wrongPhone = Not rightPhone
End Function
zeddy

20061207, 12:16 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Condition format wrong tel number (Excel 2003)
Why make it simple if you can do it in a complicated way? <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
Seriously, that is a *much* better solution, thanks! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
It can even be a oneliner:
Function WrongPhone(strPhone As String) As Boolean
WrongPhone = Not (strPhone Like "### #######")
End Function

20061207, 12:18 #7
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Condition format wrong tel number (Excel 2003)
Hi Hans,
Tx for the reply and the function. I was studying up my function and also came to the conclusion that the left and right functions were actually meaningless.
Cheers for the IF function. I will step through it using the evaluate feature to work out exactly how you went about creating it.Regards,
Rudi

20061207, 12:22 #8
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Condition format wrong tel number (Excel 2003)
Tx Zeddy and Hans....I think this is the simple solution I was after....
CHEERS!!!!Regards,
Rudi

20061208, 14:10 #9
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,354
 Thanks
 4
 Thanked 229 Times in 210 Posts
Re: Condition format wrong tel number (Excel 2003)
If you did want to do it without VBA, I think this works:
<code>=NOT(AND(LEN(A3)=11,MID(A3,4,1)=" ",LEN(SUBSTITUTE(A3," ",""))=10,ISNUMBER(SUBSTITUTE(A3," ",""))))</code>
FWIW.Regards,
Rory
Microsoft MVP  Excel