Results 1 to 9 of 9
  1. #1
    Gold Lounger
    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.

    TX
    Regards,
    Rudi

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

    Cheers
    Regards,
    Rudi

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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))

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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 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. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 481 Times in 458 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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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 one-liner:

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

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

  8. #8
    Gold Lounger
    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

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

Posting Permissions

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