Expand Hyphenated Excel Designator Ranges with Power Query
Question details
Expand hyphenated bill of materials designator ranges into individual sequential entries.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A bill of materials contains ranges such as B4-B15 and B20-B30, each of which must be listed as individual designators.
- Observed behavior
- The desired output contains every designator in each range, including both endpoints.
Check that each range has the same letter prefix at both ends, uses a hyphen as the separator, and has an ascending whole-number suffix. The query below removes leading zeros from numeric suffixes; confirm the required output format and naming convention before using it.
Expand designator ranges with an Excel Power Query
Use this query to expand one range per table row and refresh the resulting list after adding or changing source data.
The query supports letter prefixes followed by whole numbers, such as B4-B15 or AA1-AA3. Both endpoints must use the same prefix, and the end number must be at least as large as the start number.
In Excel, enter Range as a column header and put each designator range beneath it. Select the data, choose Insert > Table, and name the table BOMRanges on the Table Design tab.
Select a cell in the table and choose Data > From Table/Range. In the Power Query Editor, select Home > Advanced Editor.
Replace the existing code in Advanced Editor with the following, then select Done: let Source = Excel.CurrentWorkbook(){[Name="BOMRanges"]}[Content], MakeLists = Table.TransformColumns(Source, {{"Range", each let parts = Text.Split(Text.Trim(Text.From(_)), "-") in if List.Count(parts) <> 2 then error "Each range must have two endpoints" else let firstText = Text.Trim(parts{0}), lastText = Text.Trim(parts{1}), prefix = Text.Select(firstText, {"A".."Z", "a".."z"}), lastPrefix = Text.Select(lastText, {"A".."Z", "a".."z"}), firstNumber = Number.FromText(Text.Range(firstText, Text.Length(prefix))), lastNumber = Number.FromText(Text.Range(lastText, Text.Length(lastPrefix))) in if prefix = "" or prefix <> lastPrefix or lastNumber < firstNumber or Number.RoundDown(firstNumber) <> firstNumber or Number.RoundDown(lastNumber) <> lastNumber then error "Check the prefixes and numeric endpoints" else List.Transform(List.Numbers(firstNumber, lastNumber - firstNumber + 1), each prefix & Text.From(_)) }}), Expanded = Table.ExpandListColumn(MakeLists, "Range") in Expanded
Select Home > Close & Load to place the expanded entries in Excel. After editing or adding ranges to the input table, select Data > Refresh All.
List sequential designators with WPS Spreadsheet
For a small number of ranges, use a formula and fill it down to generate each designator in WPS Spreadsheet. Repeat the formula for each range.
- 1. Open the spreadsheet: Open WPS Spreadsheet and create a workbook or open the BOM file.
- 2. Enter the first formula: In an empty cell, enter ="B"&ROW(A4) to produce B4.
- 3. Fill the first range: Drag the cell's fill handle down through 12 cells total to produce B4 through B15.
- 4. Add the next range: In the next empty cell, enter ="B"&ROW(A20), then drag the fill handle down through 11 cells total to produce B20 through B30. Adjust the prefix, starting row reference, and fill length for other ranges.
Frequently Asked Questions
Does the Power Query include both ends of a range?
Yes. For B4-B15, the query generates B4 through B15, including both B4 and B15.
How do I update the expanded list after adding ranges?
Add each range as a new row in the BOMRanges table, then select Data > Refresh All in Excel.
Can the query handle prefixes beyond Z?
It can handle a fixed multi-letter prefix, such as AA1-AA3. It does not expand a range that changes prefixes, such as Z1-AA3.
Will the query preserve leading zeros in designators?
No. The query converts numeric suffixes to numbers, so B04-B06 becomes B4, B5, and B6. The query must be adapted if leading zeros are required.
