Translate

Sunday, July 22, 2012

Parsing Telephone Numbers VBA code in Excel 2010

A frequent task many people encounter when working with lists of data in Excel is splitting telephone numbers into the area code, phone number, and extension components. For example, a user may have a list of telephone numbers like "(913) 555-1212 X1122" and need to split them up into 3 or 4 columns containing the area code (913), the prefix (555), the number (1212) and the extension (X1122). This page describes a VBA procedure for accomplishing this.

The procedure on this page can accept a telephone number in a variety of formats, as described below. However, it can only use NANP (USA and Canada) phone numbers, and cannot accept International Dialing Codes.

Number Style
Description
5551212
Local telephone number with no area code, and no delimiters
555-1212
Local telephone number with no area code, and dash delimiter
9135551212
Full telephone number without delimiters
913-555-1212
Full telephone number with two dash delimiters
913-5551212
Full telephone number with one dash delimiter
(913)5551212
Full telephone number with area code parentheses, and no dashes
(913)555-1212
Full telephone number with area code parentheses and dash delimiter.

In addition, all of these telephone number types may include an extension number, e.g., 913-555-1212 X1234, provided that the extension is delimited by a single character, e.g., "X". Finally, all of these formats may included embedded blanks.
The ParsePhoneNumbers function will return an array of up to four elements for each telephone number. You can specify what you want returned with the SplitWhat argument to the function. This argument can be 1, 2, or 3. The values are described below. Here, the pipe character | indicates different cells.



Value
Elements
Cell 1
Cell 2
Cell 3
Cell 4
1
2
913
555-1212 x1234
2
3
913
555-1212
x1234
3
4
913
555
1212
x1234

This procedure accepts a full telephone, as described above, and returns an array of up to four elements -- the Area Code, the Prefix, the Number, and the Extension. Therefore, you will need to enter this as an Array Formula. Suppose you want the four parts of the full telephone number in A2 to be returned to B2:E2. First, select cells B2:E2, enter =ParsePhoneNumbers(A2) and press Ctrl+Shift+Enter rather than just Enter.
Below is the code for the ParsePhoneNumbers function. You must also include the NoSpaceString function, below, since this is used by the ParsePhoneNumbers function.

Function ParsePhoneNumbers(FullNum As String, _ SplitWhat As Integer, _
Optional ExtDelim) As Variant

Dim PhoneNum As String
Dim AreaCode As String
Dim Prefix As String
Dim Num As String
Dim Ext As String
Dim Pos As Integer

Const cSplitAcOnly = 1 ' aaa | ppp-nnnnXeeee
Const cSplitAcNumber = 2 ' aaa | ppp-nnnn | Xeeee
Const cSplitAll = 3 ' aaa | ppp | nnnn | Xeeee

PhoneNum = NoSpaceString(FullNum)

If Not IsMissing(ExtDelim) Then
If Len(ExtDelim) > 0 Then
Pos = InStr(1, UCase(PhoneNum), UCase(ExtDelim), _
vbTextCompare)
If Pos Then
Ext = Right(PhoneNum, Len(PhoneNum) - Pos + 1)
PhoneNum = Left(PhoneNum, Pos - 1)
End If
End If
End If


Select Case Len(PhoneNum)
Case 7, 8
AreaCode = ""
Prefix = Left(PhoneNum, 3)
Num = Right(PhoneNum, 4)

Case 10
AreaCode = Left(PhoneNum, 3)
Prefix = Mid(PhoneNum, 4, 3)
Num = Right(PhoneNum, 4)

Case 11
AreaCode = Left(PhoneNum, 3)
Prefix = Mid(PhoneNum, 5, 3)
Num = Right(PhoneNum, 4)

Case 12
If Left(PhoneNum, 1) = "(" Then
AreaCode = Mid(PhoneNum, 2, 3)
Prefix = Mid(PhoneNum, 6, 3)
Num = Right(PhoneNum, 4)
Else
AreaCode = Left(PhoneNum, 3)
Prefix = Mid(PhoneNum, 5, 3)
Num = Right(PhoneNum, 4)
End If

Case 13
AreaCode = Mid(PhoneNum, 2, 3)
Prefix = Mid(PhoneNum, 6, 3)
Num = Right(PhoneNum, 4)
Case Else
SplitWhat = -1

End Select

Select Case SplitWhat
Case cSplitAcOnly
ParsePhoneNumbers = _
Array(AreaCode, Prefix & "-" & Num & " " & Ext, "", "")

Case cSplitAcNumber
ParsePhoneNumbers = _
Array(AreaCode, Prefix & "-" & Num, Ext, "")

Case cSplitAll
ParsePhoneNumbers = Array(AreaCode, Prefix, Num, Ext)
Case Else
ParsePhoneNumbers = _
Array(CVErr(xlErrValue), CVErr(xlErrValue), _
CVErr(xlErrValue), CVErr(xlErrValue))

End Select

End Function

Function NoSpaceString(S As String) As String
NoSpaceString = _
Application.WorksheetFunction.Substitute(S, " ", "")
End Function
Below are some tables that illustrates how the function will split apart telephone numbers.


Since this function returns an array, you can use the INDEX worksheet function to retrieve any part of the function. For example, the function.
=INDEX(ParsePhoneNumbers(A1,1,"X"),2)
will return only the local phone number 555-1212 of the phone number in A1.

No comments:

Share It

Related Posts Plugin for WordPress, Blogger...