Using Excel to Convert a Number List to Dashed String of Ranges

Quote of the Day

Gratitude is riches. Complaint is poverty.

— Doris Day. This quote is another expression of the Benedictine philosophy.

Figure 1: Yellow highlight shows the function output for the number list shown.

While working on a test report for an aircraft manufacturer this week, I needed to convert a large number of number lists to strings of dashed ranges. For illustration, suppose you are given a list {1, 2, 3, 5, 6, 7, 8, 10}. Converting this list to a dashed set of ranges means generating the string "1-3, 5, 6-8, 10." Figure 1 shows another example with an optional prefix added to each number.

The need for this operation is more common than you might think. I usually see the dashed string format used on Bills of Materials (BOMs) for listing out the reference designators for specific part numbers, like resistors. A PCB BOM might have multiple 10Ω resistors with reference designators listed as "R1-R3, R5, R6-R8, R10." Normally, the schematic capture system would generate the reference designators in this format automatically, but I have worked in situations where the parts lists were in Excel and you needed to convert lists to list of dashed ranges.

Today's situation involved creating a dashed range list of the various graphs in a test report that had calibration and data charts interspersed. The customer wanted their data formatted this way and I complied – the life of a contractor.

My workbook is attached here. The VBA code is shown below.

Option Explicit
' PROGRAM:  Generate Dashed Range String from Cells Containing Numbers
' AUTHOR:   Mark Biegert
' FUNCTION: Given a range of cells containing numbers, this function
'           will convert the numbers to dashed ranges.
' INPUTS:   r range of cells containing integers
' OUTPUT:   String containing dashed ranges of numbers, separated by commas
' REVISION: 1.0
'
Function DashedRange(ByVal r As Range, Optional ByVal p As String = "") As String
' Variable Definitions
Dim s As String     'String I am building of integer ranges
Dim cnt             'Number of consecutive integers
Dim i               'Iteration variable

'Initializations
s = p & r.Value2(1, 1)  'The first number is always in the list.
cnt = 1                 'The first number is the first in a potential sequence

'Iteration
For i = 2 To r.Count
    If i <> r.Count Then                                            'We are not at the last number
        If r.Value2(i, 1) <> r.Value2(i - 1, 1) + 1 Then            'We have detected the end of a range
           If cnt = 1 Then
              s = s & ", " & p & r.Value2(i, 1)                     'If the number is isolated, just put a comma after it and start the next potential range.
           Else
              s = s & "-" & p & r.Value2(i - 1, 1) & ", " _
              & p & r.Value2(i, 1)                                  'The number is not isolated, I need to create a range.
              cnt = 1                                               'Restart my range length count
           End If
        Else
           cnt = cnt + 1                                            'Consecutive number. Just increment range count and move on.
        End If
    Else
        If r.Value2(r.Count, 1) = r.Value2(r.Count - 1, 1) + 1 Then 'We are at the last number of the input list
           s = s & "-" & p & r.Value2(r.Count, 1)                   'The last number is part of a range, finish it off as a range.
        Else
           If cnt <> 1 Then                                         'A range was going on, but the last number is not consecutive.
              s = s & "-" & p & r.Value2(r.Count - 1, 1) & ", " _
              & p & r.Value2(r.Count, 1)
           Else                                                     'The last two numbers were isolated, put in string as isolated.
              s = s & ", " & p & r.Value2(r.Count, 1)
           End If
        End If
    End If
Next i
DashedRange = s
End Function