Separating commas and parenthesis

[Silence]

Hello,

I'm trying to separate text that have commas in between. I've got a column that contains commas and a few cells in those columns have commas and bracket. The problem occurs when there are more than two values WITHIN in the bracket that are separated by commas. How can parse the text in such a way where what ever is within the bracket remains in tact? For example: Controls, Motors, Transformers (LVoltage, High Performance, Medium Voltage). The goal is to separate everything before a comma but for Transformers I would like it to remain as 'Transformers (LVoltage, High Performance, Medium Voltage)'.

Any ideas?

Thanks,
Sharjeel

 

[iliace]

Select your range and try this code - NOTE, it will overwrite everything to the right of the column you select. Try it on a copy of your data first.

Public Sub SplitByComma()
  Dim i As Long, j As Long, k As Long
  Dim rng As Excel.Range
  Dim c As Excel.Range
  Dim s As String
  Dim sNew As String
  Dim v As Variant
  
  Dim skip As Boolean
  
  If TypeName(Selection) <> "Range" Then Exit Sub
  
  Set rng = Selection
  
  For Each c In rng.Cells
    s = c.Value & ","
    j = 0
    
    For i = 1 To Len(s)
      If Mid$(s, i, 1) = "," Then j = j + 1
    Next i
    
    ReDim v(1 To j + 2)
    k = 1
    
    For i = 1 To Len(s)
      If Mid$(s, i, 1) = "(" Then
        skip = True
        sNew = sNew & "("
      ElseIf Mid$(s, i, 1) = ")" Then
        skip = False
        sNew = sNew & ")"
      ElseIf Mid$(s, i, 1) = "," And Not skip Then
        v(k) = Trim$(sNew)
        k = k + 1
        sNew = ""
      Else
        sNew = sNew & Mid$(s, i, 1)
      End If
    Next i
    c.Offset(0, 1).Resize(1, j).Value = v
  Next c
End Sub

 

[Silence]

iliace,

It worked! Thank you so very much!! I'm going to try and understand the code now. But thank you so much!! If u're ever in the Toronto/Mississauga area, I owe you one man!

 

[iliace]

Hope this helps. The basic principle is to do a manual split (similar to the split function), the only difference being to not split if you are within the parentheses. The code has a skip mode trigger, which does just that. See inline comments (in green).

Public Sub SplitByComma()
  [COLOR="SeaGreen"]'''''''''''''''''' DECLARATION SECTION ''''''''''''''''''[/COLOR]
  [COLOR="SeaGreen"]' string position pointer[/COLOR]
  Dim i As Long
  [COLOR="SeaGreen"]' array size[/COLOR]
  Dim j As Long
  [COLOR="SeaGreen"]' array position pointer[/COLOR]
  Dim k As Long
  
  [COLOR="SeaGreen"]' selected range[/COLOR]
  Dim rng As Excel.Range
  
  [COLOR="SeaGreen"]' individual cell in selected range[/COLOR]
  Dim c As Excel.Range
  
  [COLOR="SeaGreen"]' complete value of current cell[/COLOR]
  Dim s As String
  
  [COLOR="SeaGreen"]' intermediate value for each new cell[/COLOR]
  Dim sNew As String
  [COLOR="SeaGreen"]' array where all new cell values are stored[/COLOR]
  Dim v As Variant
  
  [COLOR="SeaGreen"]' skip mode (...) tracker[/COLOR]
  Dim skip As Boolean
  
  
  [COLOR="SeaGreen"]'''''''''''''''''' EXECUTION SECTION ''''''''''''''''''[/COLOR]
  [COLOR="SeaGreen"]' set range to current selection[/COLOR]
  If TypeName(Selection) <> "Range" Then Exit Sub
  Set rng = Selection
  
  [COLOR="SeaGreen"]' go through each cell in selected range[/COLOR]
  For Each c In rng.Cells
    [COLOR="SeaGreen"]' get the cell value, adding a comma at the end[/COLOR]
    s = c.Value & ","
    
    [COLOR="SeaGreen"]' reset array counter[/COLOR]
    j = 0
    
    [COLOR="SeaGreen"]' count the number of commas in the string[/COLOR]
    [COLOR="SeaGreen"]' ignoring parentheses for now[/COLOR]
    For i = 1 To Len(s)
      If Mid$(s, i, 1) = "," Then j = j + 1
    Next i
    
    [COLOR="SeaGreen"]' size the array to allow for each cell value[/COLOR]
    ReDim v(1 To j + 2)
    
    [COLOR="SeaGreen"]' reset array pointer[/COLOR]
    k = 1
    
    [COLOR="SeaGreen"]' go through the string[/COLOR]
    For i = 1 To Len(s)
      [COLOR="SeaGreen"]' if open parenthesis...[/COLOR]
      If Mid$(s, i, 1) = "(" Then
        [COLOR="SeaGreen"]' will skip commas[/COLOR]
        skip = True
        [COLOR="SeaGreen"]' add parenthesis to current value[/COLOR]
        sNew = sNew & "("
      [COLOR="SeaGreen"]' if closing parenthesis...[/COLOR]
      ElseIf Mid$(s, i, 1) = ")" Then
        [COLOR="SeaGreen"]' stop skipping commas[/COLOR]
        skip = False
        [COLOR="SeaGreen"]' add parenthesis to current value[/COLOR]
        sNew = sNew & ")"
      [COLOR="SeaGreen"]' if comma AND we're not in skipping mode...[/COLOR]
      ElseIf Mid$(s, i, 1) = "," And Not skip Then
        [COLOR="SeaGreen"]' save current value to array[/COLOR]
        v(k) = Trim$(sNew)
        [COLOR="SeaGreen"]' move the array pointer[/COLOR]
        k = k + 1
        [COLOR="SeaGreen"]' reset current value[/COLOR]
        sNew = ""
      [COLOR="SeaGreen"]' for all other characters...[/COLOR]
      Else
        [COLOR="SeaGreen"]' add the character to current value[/COLOR]
        sNew = sNew & Mid$(s, i, 1)
      End If
    Next i
    [COLOR="SeaGreen"]' put the resulting array starting one cell to the right[/COLOR]
    [COLOR="SeaGreen"]' of current cell value[/COLOR]
    c.Offset(0, 1).Resize(1, j).Value = v
  Next c
End Sub

 

[Silence]

thanks a bunch iliace! The comments helped a lot in understanding the process!!

 

[Silence]

Iliace, I noticed an error. This is what I have in the first cell:

Drives, Human Machine Interfaces (operator panels), Machine Safety, Networking (industrial and wireless communication), Programmable Logic Controllers (PLCs)

After running the code, this is what I obtain in the 2nd cell:

Programmable Logic Controllers (,Drives, Human Machine Interfaces (operator panels)

Funny thing is, the rest seem to be fine.

What are your thoughts?

 

[iliace]

Seems to work fine for me. Can you give me the line before and after the one that comes out wrong?

The one thing I can think of is if you have an open parenthesis but no closed parenthesis somewhere. If this is the case, try adding the following line at the beginning of the For Each c in Rng.Cells loop:

skip = False

But even then, I can't come up with a condition that would cause what you're describing.

 

[Silence]

The line before:
<TABLE style="WIDTH: 527pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=703 border=0 x:str><COLGROUP><COL style="WIDTH: 527pt; mso-width-source: userset; mso-width-alt: 22496" width=703><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 527pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" width=703 height=19>Programmable Logic Controllers (PLCs)</TD></TR></TBODY></TABLE>

The line after:
<TABLE style="WIDTH: 527pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=703 border=0 x:str><COLGROUP><COL style="WIDTH: 527pt; mso-width-source: userset; mso-width-alt: 22496" width=703><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 527pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" width=703 height=19>Programmable Logic Controllers (PLCs), Identification Systems (RFID)</TD></TR></TBODY></TABLE>

 

[iliace]

Still working. Entered the sample data in A1:A3, selected A1:A3, and ran the macro as posted above. Here is my result:

Excel Workbook
	A	B	C	D	E	F
1	Programmable Logic Controllers (PLCs)	Programmable Logic Controllers (PLCs)
2	Drives, Human Machine Interfaces (operator panels), Machine Safety, Networking (industrial and wireless communication), Programmable Logic Controllers (PLCs)	Drives	Human Machine Interfaces (operator panels)	Machine Safety	Networking (industrial and wireless communication)	Programmable Logic Controllers (PLCs)
3	Programmable Logic Controllers (PLCs), Identification Systems (RFID)	Programmable Logic Controllers (PLCs)	Identification Systems (RFID)
sheet

 

[iliace]

You might not have a closing parenthesis in "Programmable Logic Controllers (PLCs" - I got a similar result to yours running it like that.