Split Ranges in Excel by Reference Designators that has "-" and ","

[deejay1234]

Hello,
I have a worksheet of parts that contains ranges of reference designators, per part, that I would like split into individual reference designators.

For Example, here is a sample of the reference designator ranges:

Part Number	Description	Reference Designator
15123030	CAP CHIP 100PF 10% 50V X7R 0402	C105,C503-C505
21723266	CONN MCX JACK STR SMD	J600,J602-J603

This is how I would like it split:

Part Number	Description	Reference Designator
15123030	CAP CHIP 100PF 10% 50V X7R 0402	C105
15123030	CAP CHIP 100PF 10% 50V X7R 0402	C503
15123030	CAP CHIP 100PF 10% 50V X7R 0402	C504
15123030	CAP CHIP 100PF 10% 50V X7R 0402	C505
21723266	CONN MCX JACK STR SMD	J600
21723266	CONN MCX JACK STR SMD	J602
21723266	CONN MCX JACK STR SMD	J603

There was a same thread earlier but for some reason it does not work for me.

Split Ranges of Reference Designators

Hello, I have a worksheet of parts that contains ranges of reference designators, per part, that I would like split into individual reference designators. For Example, here is a sample of the reference designator ranges: Part Number Description Reference Designator 852852 Resistor...

www.mrexcel.com

Thanks

 

[DanteAmor]

Hi and welcome to MrExcel

Assuming the data starts in cell A2. The results in E2 onwards.

Try this macro:

Sub Split_Ranges()
  Dim i As Long, j As Long, k As Long
  Dim num1, num2a, num2b, stxt
  
  k = 1
  For i = 2 To Range("A" & Rows.Count).End(3).Row
    For Each num1 In Split(Range("C" & i), ",")
      If InStr(1, num1, "-") > 0 Then
        With CreateObject("VBScript.RegExp")
          .Global = True
          .Pattern = "\D"
          num2a = .Replace(Split(num1, "-")(0), "")
          num2b = .Replace(Split(num1, "-")(1), "")
          stxt = Replace(Split(num1, "-")(0), num2a, "")
        End With
        For j = num2a To num2b
          k = k + 1
          Range("E" & k).Value = Range("A" & i).Value
          Range("F" & k).Value = Range("B" & i).Value
          Range("G" & k).Value = stxt & j
        Next
      Else
        k = k + 1
        Range("E" & k).Value = Range("A" & i).Value
        Range("F" & k).Value = Range("B" & i).Value
        Range("G" & k).Value = num1
      End If
    Next
  Next
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[deejay1234]

Hi and welcome to MrExcel

Assuming the data starts in cell A2. The results in E2 onwards.

Try this macro:

Sub Split_Ranges()
  Dim i As Long, j As Long, k As Long
  Dim num1, num2a, num2b, stxt
 
  k = 1
  For i = 2 To Range("A" & Rows.Count).End(3).Row
    For Each num1 In Split(Range("C" & i), ",")
      If InStr(1, num1, "-") > 0 Then
        With CreateObject("VBScript.RegExp")
          .Global = True
          .Pattern = "\D"
          num2a = .Replace(Split(num1, "-")(0), "")
          num2b = .Replace(Split(num1, "-")(1), "")
          stxt = Replace(Split(num1, "-")(0), num2a, "")
        End With
        For j = num2a To num2b
          k = k + 1
          Range("E" & k).Value = Range("A" & i).Value
          Range("F" & k).Value = Range("B" & i).Value
          Range("G" & k).Value = stxt & j
        Next
      Else
        k = k + 1
        Range("E" & k).Value = Range("A" & i).Value
        Range("F" & k).Value = Range("B" & i).Value
        Range("G" & k).Value = num1
      End If
    Next
  Next
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

Hi Dante,

That totally worked! If its not too much of a hassle could you include comments on the code so I could understand it a bit?

Thanks

 

[DanteAmor]

Before reviewing, I give a brief description of RegEx:
RegEx stands for "Regular Expression" in VBA Excel and is a sequence of characters that defines the search pattern for finding a specific pattern of characters in a string of values.

Code with comments:

Sub Split_Ranges()
  
  'SECTION Variable declaration
  Dim i As Long, j As Long, k As Long
  Dim num1, num2a, num2b, stxt
  
  'Initial counter for the destination row
  k = 1
  
  'Cycle in column A, starting at 2 and going to the last row with data
  For i = 2 To Range("A" & Rows.Count).End(3).Row
  
    'For each item separated by comma
    For Each num1 In Split(Range("C" & i), ",")
    
      'Check if the item has "-"
      If InStr(1, num1, "-") > 0 Then
      
        'With RegEx it eliminates everything that is not a number.
        With CreateObject("VBScript.RegExp")
          .Global = True
          .Pattern = "\D"
          
          'If it contains "-" then it has 2 parts,
          'The left part goes to the variable num2a
          'The right part goes to the variable num2b
          
          'With the function Split and (0) you get the first part (before the "-")
          'With the function Split and (1) you get the second part (after the "-")
          num2a = .Replace(Split(num1, "-")(0), "")
          num2b = .Replace(Split(num1, "-")(1), "")
          
          ' Gets the letters that come before the number.
          stxt = Replace(Split(num1, "-")(0), num2a, "")
        End With
        
        'For each number from the initial number before the hyphen to the final number after the hyphen.
        For j = num2a To num2b
          'Increase the destination row
          k = k + 1
          
          'Pass values from column A and B to column E and F
          Range("E" & k).Value = Range("A" & i).Value
          Range("F" & k).Value = Range("B" & i).Value
          
          'Passes the concatenated value of the letters and consecutive to column G
          Range("G" & k).Value = stxt & j
        Next
      Else
      
        'If the item does not contain "-"
        k = k + 1
        
        'Pass values from column A and B to column E and F
        Range("E" & k).Value = Range("A" & i).Value
        Range("F" & k).Value = Range("B" & i).Value
        
        'Pass item to column G
        Range("G" & k).Value = num1
      End If
    Next
  Next
End Sub

 

[alansidman]

A simple alternative with Power Query, aka Get and Transform Data and found on the Data Tab

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Reference Designator", Splitter.SplitTextByAnyDelimiter({",","-"}, QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Reference Designator")
in
    #"Split Column by Delimiter"

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

The Complete Guide to Installing Power Query - Excel Campus

Learn what version you need to get Power Query and instructions on how to install this free add-in from Microsoft.

www.excelcampus.com

- Follow this link for an introduction to Power Query functionality.

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports and more.

www.excelcampus.com

- Follow this link for a video which demonstrates how to use Power Query code provided.

Power Query - How To Paste Code (video) - Excel Solutions

Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it. Here’s a short video...

excel.solutions

Book2
	A	B	C
1	Part Number	Description	Reference Designator
2	15123030	CAP CHIP 100PF 10% 50V X7R 0402	C105,C503-C505
3	21723266	CONN MCX JACK STR SMD	J600,J602-J603
4
5	Part Number	Description	Reference Designator
6	15123030	CAP CHIP 100PF 10% 50V X7R 0402	C105
7	15123030	CAP CHIP 100PF 10% 50V X7R 0402	C503
8	15123030	CAP CHIP 100PF 10% 50V X7R 0402	C505
9	21723266	CONN MCX JACK STR SMD	J600
10	21723266	CONN MCX JACK STR SMD	J602
11	21723266	CONN MCX JACK STR SMD	J603
Sheet1