Use VBA to parse out undesired strings from each cell in a column, then group column results

[RLloyd]

Column C has cells containing dozens to hundreds of control identifiers, with a comma delimiter. Each control has two letters, a hyphen, and numerical identifier. I have VBA script that alphabetizes the contents of each cell, but I need to parse out all the controls in each cell that don't start with the desired first two letters, e.g., JT. So if cell C1 has controls AB-2.1, AB-4.6, GG-3.6, GG-4(2).1, JT-9.1, JT-10(3).4, I need to delete the non-JT controls from each cell. Note: when alphabetizing, the () come last, e.g., JT-9, JT-9(4) Editing the code for different controls on other sheets is easy enough. With hundreds of cells, each with dozens to hundreds of controls, and that I'll have to use this code weekly for years, I consider using VBA appropriate.

One last request, if you will indulge me... I would also like to copy all the remaining JT codes in column C and group them in cell Z1.

Assistance is always appreciated. RL

 

[DanteAmor]

Do you want this in Z1:
AB-2.1, AB-4.6, GG-3.6, GG-4 (2) .1

In C1 do you want this:
JT-9, JT-9 (4)

But how do you get from this JT-9.1, JT-10(3).4 to this JT-9, JT-9 (4)

 

[RLloyd]

Thank you for offering to help.

The code will leave only controls that begin with JT in each cell in column C. Then it will copy the contents of all cells in column C (the JT controls) and copy them into cell Z1. Please pardon my examples that you questioned, where I should have been consistent in the examples I used.

 

[DanteAmor]

Use this macro

Sub Parse()
    Dim c As Range, cs As Variant, j As Long, cad As String, dt As String
    For Each c In Range("C1", Range("C" & Rows.Count).End(xlUp))
        cad = ""
        cs = Split(c.Value, ",")
        For j = 0 To UBound(cs)
            dt = WorksheetFunction.Trim(cs(j))
            If Left(dt, 2) = "JT" Then
                cad = cad & dt & ", "
            End If
        Next
        Cells(c.Row, "Z").Value = Left(cad, Len(cad) - 2)
    Next
    MsgBox "End"
End Sub

 

[RLloyd]

I tried your code today, but it has a bug in the line - Cells(c.Row, "Z").Value = Left(cad, Len(cad) - 2) Would it be easier to remove the code that combines the results in Z1?

Thanks, RL

 

[DanteAmor]

What does the error say? Some rows were successful? if so, add this line to the beginning of the macro:
On Error Resume Next