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

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```
This entry was posted in Excel. Bookmark the permalink.