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:

nnn-nnnnnnn-nnn-nnnn1-nnn-nnn-nnnn1-nnn-nnnnnnn-nnnn xnnnn

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:

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

2. Any extensions will be listed as xnnnnn following the phone number.

3. 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)   Else      strPhone = strTemp   End If      If Left(strPhone, 1) = "1" Then      strPhone = Mid(strPhone, 2)   End If      If Len(strPhone) <> 7 And Len(strPhone) <> 10 Then      MsgBox "Please enter a valid telephone number.", vbExclamation	Cancel = True      Exit Sub   End If      '   ' Prepend the default area code   '   If Len(strPhone) = 7 Then      strPhone = DefaultAreaCode & strPhone   End If      '   ' 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   End If   End Sub

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:

• 5551212?will prepend 703 and create (703) 555-1212.
• 15551212?same result as previous case.
• 5551212 x 1 2 3 4?becomes (703) 555-1212 x1234?spaces are removed.
• 17035551212 x 1234?same as previous case.
• Any numbers that aren’t seven or 10 digits?trapped 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 time?and no doubt?money.