any of the more common questions asked of the VB Pro involve formatting and validating data. In this 10-Minute Solution, you'll learn how to validate and format a phone number. While we won't deal with international (non-US) phone numbers here, you can apply these techniques to your local phone number format.
In the United States, there are a number of valid formats for phone numbers that might be entered into a form:
There are many other variations of this, as well, but those examples should be enough to give you an idea of what we're dealing with here. Because there are so many valid formats, you're going to have to set some rules for reformatting these phone numbers:
All numbers will at least be in the format (nnn) nnn-nnnn. Numbers entered without area codes will have a default area code prepended to them.
Any extensions will be listed as xnnnnn following the phone number.
If an initial 1 is added to the number, it will be removed, since users already know to dial a one before making a long distance call.
With these rules in mind, let's create the validation code for a text box named txtPhone:
Private Sub txtPhone_Validate(Cancel As Boolean)
Dim strTemp As String
Dim strPhone As String
Dim strExtension As String
Dim intResult As Integer
Const DefaultAreaCode = "703"
' Remove all the grouping characters for
' now. We'll add them back in later.
strTemp = Replace(txtPhone, "(", "")
strTemp = Replace(strTemp, ")", "")
strTemp = Replace(strTemp, "-", "")
strTemp = Replace(strTemp, " ", "")
strTemp = Replace(strTemp, "X", "x")
' Break up the digits into the number and
' the extension, if any.
intResult = InStr(1, strTemp, "x", vbTextCompare)
If intResult > 0 Then
strExtension = Mid(strTemp, intResult + 1)
strPhone = Left(strTemp, intResult - 1)
strPhone = strTemp
If Left(strPhone, 1) = "1" Then
strPhone = Mid(strPhone, 2)
If Len(strPhone) <> 7 And Len(strPhone) <> 10 Then
MsgBox "Please enter a valid telephone number.", vbExclamation
Cancel = True
' Prepend the default area code
If Len(strPhone) = 7 Then
strPhone = DefaultAreaCode & strPhone
' Build the new phone number
txtPhone = "(" & Left(strPhone, 3) & ") " _
& Mid(strPhone, 4, 3) & "-" _
& Right(strPhone, 4)
' Add the extension, if any
If strExtension <> "" Then
txtPhone = txtPhone & " x" & strExtension
I used a default area code of 703, which you can obviously change as needed. If you're going to be using the application in different locations, you may want to store that information with the application user's profile, similar to the way that Windows stores your favorite locations for dialing.
The code first removes all the grouping and separator characters, except for the X, which marks the beginning of an extension. It was best to convert uppercase X's to lowercase x's for purposes of comparison.
Next, the code parses out the phone number and the extension. If there is a leading 1, it's removed. The phone system is such that you can't have a 1 as the first number of either an area code or a phone number prefix.You will then either have a ten-digit phone number or a seven-digit phone number. If you don't have either one, you have an error and bail out immediately.
For seven-digit phone numbers, we prepend the default area code, and then build the final number with a little bit of string manipulation. The extension found, if any, is added back to the number. To try this code out, you can use any of these test cases to see the validation in action:
- 5551212will prepend 703 and create (703) 555-1212.
- 15551212same result as previous case.
- 5551212 x 1 2 3 4becomes (703) 555-1212 x1234spaces are removed.
- 17035551212 x 1234same as previous case.
- Any numbers that aren't seven or 10 digitstrapped error.
The end result is that it makes data entry easier for the user. The computer is now smart enough to automatically translate data into the format that has been pre-selected for use in that system, saving timeand no doubtmoney.