# Thread: Condition format wrong tel number (Excel 2003)

1. ## Condition format wrong tel number (Excel 2003)

Hi all,

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.

TX

2. ## 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 multi-nested expression??

Cheers

3. ## 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))

4. ## 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 user-defined 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)

5. ## Re: Condition format wrong tel number (Excel 2003)

Hi Hans

Function WrongPhone(strPhone As String) As Boolean

rightPhone = strPhone Like "### ######"
wrongPhone = Not rightPhone

End Function

zeddy

6. ## 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 one-liner:

Function WrongPhone(strPhone As String) As Boolean
WrongPhone = Not (strPhone Like "### #######")
End Function

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

8. ## Re: Condition format wrong tel number (Excel 2003)

Tx Zeddy and Hans....I think this is the simple solution I was after....

CHEERS!!!!

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•