Arrays can be handy, and there are some excellent functions in VBA-Land to help us deal with arrays, but not so much with simple delimited lists. They must be first converted to arrays before they become useful in VBA.

A simple telephone number can be considered a delimited list. It consists of three parts delimited by a dash (-). Thus, the telephone number 800-555-1212 has the following parts:

  1. Area Code: 800
  2. Exchange: 555
  3. Number: 1212

If you want to get just the Exchange portion, you can use the Split() function to convert the phone number into a three-element array, then retrieve the second element. That’s not so hard – until you need to run some code to return only phone numbers from a list with a given Exchange. It’s a bit cumbersome to write the VBA code for such a situation, and next to impossible for a SQL query. For one thing, SQL doesn’t do arrays, making it supremely difficult to get a specific element without migraines and extra caffeine.

It can get even hairier if the format used includes multiple delimiters, such as 800/555-1212.

If you (like I do) need to perform such analyses on a regular basis, the GetElement() function may be of use to you. GetElement() essentially does nothing more than what was explained above – it converts the string to an array and returns a selected element. Here is the code:

Function GetElement(LineVal As String, WhatElement As Integer, Optional Delimiter = "|") As String

Dim astrParts As Variant

On Error GoTo errHandler

‘ Create an array from the input.
astrParts = Split(LineVal, Delimiter)

‘ Get the selected array element (zero-based)
GetElement = astrParts(WhatElement - 1)

Exit Function

errHandler:

[Put your favorite error handling code here]

End Function

With this simple function, you can write a query such as the following to retrieve the Exchange element from dash-delimited numbers (800-555-1212):

SELECT GetElement([Phone],2,"-") AS Exchange FROM Customers WHERE GetElement([Phone],2,"-");

Even with multiple delimiters, it is not particularly tough. Take 800/555-1212. The first element is the Area Code (800), delimited by a forward slash. The first element of the second element (555-1212) is the Exchange (555), delimited by a dash. So we modify the query as follows:

SELECT GetElement(GetElement([Phone],2,”/”),1,”-“) AS Exchange FROM Customers WHERE GetElement(GetElement([Phone],2,”/”),1,”-“) = 555

I use this in Excel, as well, in my Personal Macro Workbook.  

Happy coding!