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:
- Area Code: 800
- Exchange: 555
- 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)
[Put your favorite error handling code here]
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.