VBA to extract all words that start with (CB and end with )

[sjinvestigator]

Hi All,

How would one go about extracting all text that starts with "(CB" and ends with ")" from a column of cells with a bunch of text and paste the result in the next column? For example, if I have this in A2:

* Margarine Wholesale (27), Margarine Retail (241), Margarine Retail (287), Margarine Bulk (1781) (CB28412)
* Margarine Wholesale (281), Margarine Retail (42), Margarine Retail (13), Margarine Bulk (27) (CB28412)
* Margarine Wholesale (19), Margarine Retail (281), Margarine Retail (411), Margarine Bulk (3814) (CB82474)

I'd like to update B2 with:
(CB28412),(CB82474)

...excluding all other text found in A2. Repeat for as many rows in column A as there is data. I'd also like it to avoid duplicates if the same (CBxxxx number appears more than once in the string.

I found an excelent post with a solution for a very similar ask on this forum and tried to modify the code as follows:

Sub Testing()
  Dim N As Long, X As Long, Cell As Range, Arr1 As Variant, Arr2 As Variant
  Columns("B").Clear
  For Each Cell In Range("A1", Cells(Rows.Count, "A"))
    TotalString = ""
    Arr1 = Split(Cell, "(")
    For X = 1 To UBound(Arr1)
      If UCase(Arr1(X)) Like "CB*" Then
        Arr2 = Split(Arr1(X), "(CB")
        N = N + 1
        TotalString = TotalString & "," & "(" & Arr2(0) ' & "KB"
        Cells(Cell.Row, "B") = TotalString
        End If
    Next
  Next
End Sub

However, when I run it, it extracts the (CB numbers correctly but also leaves in some other text that I don't want. It also doesn't filter duplicates.

Could anyone please offer me some advice? Thank you so much!

Mini sheet:

Scancode_Example.xlsx
	A	B
1	Scancodes & Type	Output I want
2	* Margarine Wholesale (27), Margarine Retail (241), Margarine Retail (287), Margarine Bulk (1781) (CB28412) * Margarine Wholesale (281), Margarine Retail (42), Margarine Retail (13), Margarine Bulk (27) (CB28412) * Margarine Wholesale (19), Margarine Retail (281), Margarine Retail (411), Margarine Bulk (3814) (CB82474)	(CB28412),(CB82474)
3	* Margarine Wholesale (11), Margarine Retail (27), Margarine Retail (14), Margarine Bulk (15) (CB91254) * Margarine Wholesale (6), Margarine Retail (399), Margarine Retail (400), Margarine Bulk (51) (CB49185)	(CB91254),(CB49185)
Data

 

[Peter_SSs]

Thank you for taking the time to provide this code. I got some inconsistent results when I tried it on large datasets

Are you able to give a sample or two of cells where Zot's code failed to give the correct results?
If that could be resolved, and you have large data, I think a significantly quicker code might be possible.