Ultimate phone number formatting

[jimday1982]

Here's a tough one for you. I have a field named "buyer-phone-number" with phone numbers in varying formats, see below:

1111111111
111 111 1111
111 111-1111
111-111-1111
111.111.1111
111*111*1111
111/111-1111
(111)111-1111
(111) 111-1111

Can anyone offer any insight or code examples of how to go about formatting this data into the format below?:

(111) 111-1111

Any help is GREATLY appreciated!

 

[dsmaj]

Here's a solution

I just threw this together real quick, so it may not be squeeky-clean--I actually needed a function to do this as well You can add any criteria you want in the select statement.

Public Function FormatPhoneNumber(inputNumber As String) As String
    Dim phonenumber As String
    Dim temp As String
    Dim i As Integer
    
    phonenumber = inputNumber
    temp = ""
      
    For i = 1 To Len(phonenumber)
        Select Case Mid(phonenumber, i, 1)
            Case "("
            Case " "
            Case ")"
            Case "/"
            Case "."
            Case "*"
            Case "-"
                'do nothing
            Case Else
                temp = temp & Mid(phonenumber, i, 1)
        End Select
    Next i
    
    phonenumber = temp
    
    temp = "(" & Mid(phonenumber, 1, 3) & ")" & " " & Mid(phonenumber, 4, 3) & "-" & Mid(phonenumber, 7, 4)
    FormatPhoneNumber = temp
  
End Function

Hopefully this is what you needed...
Sam.

 

[jimday1982]

Wow! Works like a charm! One question for you though...would there be a way to display nothing (" ") if there is no phone number listed? It's giving me "#Error as of now.

Thanks!

 

[NJudson]

When looping through your table have a line that tests this:

If len(phonenumber) = 0 then phonenumber = ""

 

[jimday1982]

Hmm - that makes sense but doesn't seem to be working...I added the alphabet to the case statement and noticed that "()-" will always be present no matter what - could that be throwing it off?

 

[dsmaj]

Wow! Works like a charm! One question for you though...would there be a way to display nothing (" ") if there is no phone number listed? It's giving me "#Error as of now.

Thanks!

You could just surround the releveant parts of the code I supplied with
IF inputNumber <> "" then
' relevant code here
else
temp = ""
endif


I think this is what you're asking....

Edit: Oh, and as a side note, that function relies on having 10 input digits, regardless of what other characters are passed. So I mean you could have 555********55()()()(5...5/555 for all the function cares, as long as after parsing out all the junk characters it ends up with 10 digits. This means that if the function is passed 555-1234 you will not get the expected results. You should be able to change this easily though...

Sam.

 

[jimday1982]

Again, this makes sense as well and I am using the code below...but still no luck...does something look off to you?

Public Function FormatPhoneNumber(inputNumber As String) As String
    Dim phonenumber As String
    Dim temp As String
    Dim i As Integer
    phonenumber = inputNumber
    'test
    If inputNumber <> "" Then
    temp = ""
      For i = 1 To Len(phonenumber)
        Select Case Mid(phonenumber, i, 1)
            Case "("
            Case " "
            Case ")"
            Case "/"
            Case "."
            Case "*"
            Case "-"
            Case "#"
            Case "a"
            Case "b"
            Case "c"
            Case "d"
            Case "e"
            Case "f"
            Case "g"
            Case "h"
            Case "i"
            Case "j"
            Case "k"
            Case "l"
            Case "m"
            Case "n"
            Case "o"
            Case "p"
            Case "q"
            Case "r"
            Case "s"
            Case "t"
            Case "u"
            Case "v"
            Case "w"
            Case "x"
            Case "y"
            Case "z"
            Case Else
                temp = temp & Mid(phonenumber, i, 1)
        End Select

    Next i

    phonenumber = temp
    temp = "(" & Mid(phonenumber, 1, 3) & ")" & " " & Mid(phonenumber, 4, 3) & "-" & Mid(phonenumber, 7, 4)
    
    'test
    Else
    temp = ""
    End If
    
    FormatPhoneNumber = temp
  End Function

 

[dsmaj]

How are you passing inputNumber parameter to the function? You have to make sure you handle the NULL case in the calling code, or add apropriate code to the function to handle the NULL case.

Try IF inputNumber <> "" AND inputNumber <> NULL

Sam.

 

[jimday1982]

Sam,

I'm using the following expression in my query:

Expr6: FormatPhoneNumber([Orders from Seller Central Database]![buyer-phone-number])

I'm getting a syntax error when trying to use:

IF inputNumber <> "" AND inputNumber <> NULL

Any other suggestions?

Thanks!

 

[dsmaj]

Sam,

I'm using the following expression in my query:

Expr6: FormatPhoneNumber([Orders from Seller Central Database]![buyer-phone-number])

I'm getting a syntax error when trying to use:

IF inputNumber <> "" AND inputNumber <> NULL

Any other suggestions?

Thanks!

You need to put a THEN at the end of that IF statement

IF inputNumber <> "" AND inputNumber <> NULL THEN
.....

Sam.

 

[jimday1982]

Haha whoops. Ok, no more syntax errors, but now all my data is blank except for the few #Errors for the missing records...I think this did the exact opposite of what I need...any other ideas?

 

[dsmaj]

It's hard to say without more information...the function itself works given that it is provided the apropriate data, so you need to examine the context in which the function is called, and make sure that it's being passed the apropriate data, and that what you're trying to do is handling the returned string correctly.

If you can provide more contextual information, I might be able to help you out more...

Sam.

 

[Kodo]

the easiest way to do this would be to strip out ALL foreign characters and spaces this way ALL the phone numbers are one format then apply your formatting to the now uniform string of numbers.

 

[dsmaj]

Good point, that definitely would have been the best way to do it I just wrote that example up in a few seconds though...however I'll be doing this for my PhoneFormat code. In any event, I think the actual function was doing what it was supposed to do...have you been able to get it working Jim?

Sam.

 

[jimday1982]

Unfortunately not. I've played with it quite a bit and everything looks fine except for records in my table with a blank phone number - they show up as #Error and data that has something like "unlisted" shows up as "()-" in my query. The proposed solution above sounds great, but being a novice, I wouldn't even know where to start.

Does anyone have access to a function that does the above, but also puts nothing ("") if there is no phone number listed in the table data? Any help would be greatly appreciated!

Good point, that definitely would have been the best way to do it I just wrote that example up in a few seconds though...however I'll be doing this for my PhoneFormat code. In any event, I think the actual function was doing what it was supposed to do...have you been able to get it working Jim?

Sam.

 

[dsmaj]

Okay, here's the version of the code I'll be using (I think)

Public Function FormatPhoneNumber(ByVal inputNumber As String) As String
    Dim phonenumber As String
    Dim temp As String
    Dim i As Integer
    
    temp = ""
    
    If Not IsNull(inputNumber) Then
        phonenumber = inputNumber
      
        For i = 1 To Len(phonenumber)
            Select Case Mid(phonenumber, i, 1)
                Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9"
                    temp = temp & Mid(phonenumber, i, 1)
                Case Else
                    ' do nothing
            End Select
        Next i
    
        phonenumber = temp
        If Len(phonenumber) = 10 Then ' Area code included
            temp = "(" & Mid(phonenumber, 1, 3) & ")" & " " & Mid(phonenumber, 4, 3) & "-" & Mid(phonenumber, 7, 4)
        ElseIf Len(phonenumber) = 7 Then ' Local number only
            temp = Mid(phonenumber, 1, 3) & "-" & Mid(phonenumber, 4, 4)
        End If
    End If 'IsNull(inputNumber
    
    FormatPhoneNumber = temp
  
End Function

Try that out and let me know.

Sam.

 

[dsmaj]

By the way

By the way, make sure the input to this function is in fact a string. Your explanation of what you're doing is pretty vague, but if you're feeding the function data from a table, make sure the table field is of the string datatype.

The newest code that I posted will handle both 10 digit phone numbers and 7 digit phone numbers. If the phone number is not 10 or 7 digits, it will just return the numbers that were input with no formating.

Sam.

 

[jimday1982]

Hey Sam-

That's great - not only is the function a lot shorter...but my query returns nothing if the table data contains text (just what i needed!).

My only question is - would there be a way to return nothing if the phone number field is blank in the table?

 

[jimday1982]

Forgot to mention that my phone number field is set as text, so that should be ok, right?

 

[dsmaj]

My only question is - would there be a way to return nothing if the phone number field is blank in the table?

It should work like that right now... if not try the following instead:

Public Function FormatPhoneNumber(ByVal inputNumber As Variant) As String
    Dim phonenumber As String
    Dim temp As String
    Dim i As Integer
    
    temp = ""
    
    If Not IsNull(inputNumber) And Not IsEmpty(inputNumber) Then
        phonenumber = inputNumber
      
        For i = 1 To Len(phonenumber)
            Select Case Mid(phonenumber, i, 1)
                Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9"
                    temp = temp & Mid(phonenumber, i, 1)
                Case Else
                    ' do nothing
            End Select
        Next i
    
        phonenumber = temp
        If Len(phonenumber) = 10 Then ' Area code included
            temp = "(" & Mid(phonenumber, 1, 3) & ")" & " " & Mid(phonenumber, 4, 3) & "-" & Mid(phonenumber, 7, 4)
        ElseIf Len(phonenumber) = 7 Then ' Local number only
            temp = Mid(phonenumber, 1, 3) & "-" & Mid(phonenumber, 4, 4)
        End If
    End If 'IsNull(inputNumber)
    
    FormatPhoneNumber = temp
        
End Function

Sam.