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.

 
This entry was posted in Excel. Bookmark the permalink.

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

  1. Pingback: Using Excel to Convert a Number List to Dashed String of Ranges | Math Encounters Blog | jtveg's Blog

Leave a Reply

Your email address will not be published. Required fields are marked *