VBA: Help extracting multiple sub-strings

S

sjinvestigator

New Member
Joined
Aug 11, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
Hi all,

Hope you're doing well today. I've got a tricky problem involving parsing sub-strings that I've been struggling with for several days. I would be REALLY grateful if some kind soul could please help me get (re-)started or give me some guidance on how to approach this problem. I'd love even some simple pointers in the right direction as most of what I've tried has not worked well. I'm beginning to think this is impossible.

In a nutshell, I'm trying to write some VBA code that will loop through a column of cells that have text data similar to THIS:

* 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)

What I need to do is extract substrings that list the different types of margarine and their corresponding scancodes (CBxxxx). I want to just paste the results into another sheet.

So, for example, the result of processing the above would be a row that shows "CB28412" in Column A and "Margarine Wholesale (27), Margarine Retail (241), Margarine Retail (287), Margarine Bulk (1781), Margarine Wholesale (281), Margarine Retail (42), Margarine Retail (13), Margarine Bulk (27)" in column B.

Then the next row would have "CB82474" in column A and "Margarine Wholesale (19), Margarine Retail (281), Margarine Retail (411), Margarine Bulk (3814) (CB82474)" in column B.

Please see attached images and mini-sheets of an example of the data I am working with and expected results I want to get after running the macro on the Result tab.

On the plus side, each list of types of margarine starts with a star (*) and each scancode is in parenthesis at the end of the list of margarines. That's how I have been trying to identify each part of the substring. One the negative side, though, sometimes multiple lists of margarines apply to the same scancode and most entries have more than one scancode/list combo. I also sometimes find an extraneous line of text from our inventory system in between the lists of margarines of scancodes that need to be ignored. I only want to process the parts of the string that corresponds to lists starting with * and ending with a (CBxxxx) number

I've tried to make this as clear and representative of the data I'm working with as humanly possible without being too long or complex. I hope it's clear what I'm trying to do, but if you need any further information, please just let me know.

I want to offer my sincere thanks, in advance, to anyone who takes the time to look at this problem.

Thanks very much!!!!!

SJ

Mini sheet data sample:
Scancode_Example.xlsx
AB
1MonthScancodes & Type
2January* 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)
3February* 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)
4March* Margarine Wholesale (1008), Margarine Retail (1009), Margarine Retail (1010), Margarine Bulk (1011) (CB11888) * Margarine Wholesale (227), Margarine Retail (228), Margarine Retail (229), Margarine Bulk (230) (CB11888) extraneous text sometimes present in my data * Margarine Wholesale (583), Margarine Retail (622), Margarine Retail (902), Margarine Bulk (114) (CB31122) more extraneous text * Margarine Wholesale (501), Margarine Retail (601), Margarine Retail (999), Margarine Bulk (293) (CB82474) * Margarine Wholesale (517), Margarine Retail (683), Margarine Retail (872), Margarine Bulk (336) (CB82474)
Data


Min sheet results sample:
Scancode_Example.xlsx
AB
1ScancodeApplies To
2CB28412Margarine Wholesale (27), Margarine Retail (241), Margarine Retail (287), Margarine Bulk (1781), Margarine Wholesale (281), Margarine Retail (42), Margarine Retail (13), Margarine Bulk (27)
3CB82474Margarine Wholesale (19), Margarine Retail (281), Margarine Retail (411), Margarine Bulk (3814)
4CB91254Margarine Wholesale (11), Margarine Retail (27), Margarine Retail (14), Margarine Bulk (15)
5CB11888Margarine Wholesale (1008), Margarine Retail (1009), Margarine Retail (1010), Margarine Bulk (1011), Margarine Wholesale (227), Margarine Retail (228), Margarine Retail (229), Margarine Bulk (230)
6CB31122Margarine Wholesale (583), Margarine Retail (622), Margarine Retail (902), Margarine Bulk (114)
7CB82474Margarine Wholesale (501), Margarine Retail (601), Margarine Retail (999), Margarine Bulk (293), Margarine Wholesale (517), Margarine Retail (683), Margarine Retail (872), Margarine Bulk (336)
Result
 

Attachments

  • ExpectedResult.jpg
    ExpectedResult.jpg
    206.7 KB · Views: 7
  • SampleData.jpg
    SampleData.jpg
    238.4 KB · Views: 8

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Welcome to the MrExcel Message Board!

With the data shown, the following macro obtains the expected result.
The data in the "Data" sheet, results in the "Result" sheet

VBA Code: 
Sub extracting_multiple_substrings()
  Dim a As Variant, b As Variant, sText As Variant, ky As Variant
  Dim i As Long, j As Long, n As Long
  Dim num As String, cad As String
  Dim dic As Object
  
  Set dic = CreateObject("Scripting.Dictionary")
  a = Sheets("Data").Range("B2", Sheets("Data").Range("B" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a) * 100, 1 To 2)
  
  For i = 1 To UBound(a)
    Set dic = CreateObject("Scripting.Dictionary")
    For Each sText In Split(a(i, 1), Chr(10))
      If Left(sText, 1) = "*" Then
        n = InStrRev(sText, "(CB", , vbTextCompare)
        num = Replace(Mid(sText, n + 1, 99), ")", "")
        cad = Mid(sText, 3, n - 4)
        If Not dic.exists(num) Then
          dic(num) = cad
        Else
          dic(num) = dic(num) & ", " & cad
        End If
      End If
    Next
    
    For Each ky In dic.keys
      j = j + 1
      b(j, 1) = ky
      b(j, 2) = dic(ky)
    Next
    
  Next
  
  Sheets("Result").Range("A2").Resize(j, 2).Value = b
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (extracting_multiple_substrings) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Upvote 0
Wow, thank you SO MUCH for your help!!! This is really great! I tried it on several of my datasets and it worked perfectly on almost all of them. This has gotten me 99% of the way there. However, I did have problems with the macro when I ran it on my files that have REALLY long text strings and some carriage returns. Most of them are not this bad, but I do have some where it chokes. The error is "Invalid procedure call or argument " on cad = Mid(sText, 3, n - 4). Do you have any advice on how I could tweak your excellent macro to be able to handle the ones with huge strings with the carriage returns?

Here's an extreme example of what I'm talking about, new row 4 updated minisheet data tab.


FirstReply.xlsm
AB
1MonthScancodes & Type
2January* 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)
3February* 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)
4March* Margarine Wholesale (1008), Margarine Retail (1009), Margarine Retail (1010), Margarine Bulk (1011) (CB11888) * Margarine Wholesale (227), Margarine Retail (228), Margarine Retail (229), Margarine Bulk (230) (CB11888) extraneous text sometimes present in my data * Margarine Wholesale (583), Margarine Retail (622), Margarine Retail (902), Margarine Bulk (114) (CB31122) more extraneous text * Margarine Wholesale (501), Margarine Retail (601), Margarine Retail (999), Margarine Bulk (293) (CB82474) * Margarine Wholesale (517), Margarine Retail (683), Margarine Retail (872), Margarine Bulk (336) (CB82474)
5April* Margarine 10 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 WLK Bulk 2009 (2004), Margarine 10 FARUN Retail Bulk 2009 (2004), Margarine 10 FARUN Bulk 2009 (2004), Margarine 10 Wholesale Bulk 2009 (2004) 2021-07 Scancode Bulletin for Margarine 10 Revis 21H1 for 421-based Inventory (CB5004444) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine 10 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 WLK Bulk 2009 (800), Margarine 10 FARUN Retail Bulk 2009 (800), Margarine 10 FARUN Bulk 2009 (800), Margarine 10 Wholesale Bulk 2009 (800) 2021-07 Extracted Scancode Bulletin for Margarine 10 Revis 21H1 for 281-based Inventory (CB5004237) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine 10 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 WLK Bulk 2009 (2004), Margarine 10 FARUN Retail Bulk 2009 (2004), Margarine 10 FARUN Bulk 2009 (2004), Margarine 10 Wholesale Bulk 2009 (2004) 2021-07 Scancode Bulletin for Margarine 10 Revis 20H2 for 421-based Inventory (CB5004237) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine 10 2004 (2004), Margarine 10 Retail Bulk 2004 (2004), Margarine 10 Retail Bulk 2004 (2004), Margarine 10 WLK Bulk 2004 (2004), Margarine 10 FARUN Retail Bulk 2004 (2004), Margarine 10 FARUN Bulk 2004 (2004), Margarine 10 Wholesale Bulk 2004 (2004) 2021-07 Extracted Scancode Bulletin for Margarine 10 Revis 2004 for 421-based Inventory (CB5004237) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine Triapod 2004 (800), Margarine Triapod Standard Bulk 2004 (800), Margarine Triapod Wholesaler Bulk 2004 (800) 2021-07 Scancode Bulletin for Margarine Triapod, Revis 2004 for 281-based Inventory (CB5004237) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine 10 2004 (800), Margarine 10 Retail Bulk 2004 (800), Margarine 10 Retail Bulk 2004 (800), Margarine 10 WLK Bulk 2004 (800), Margarine 10 FARUN Retail Bulk 2004 (800), Margarine 10 FARUN Bulk 2004 (800), Margarine 10 Wholesale Bulk 2004 (800) 2021-07 Extracted Scancode Bulletin for Margarine 10 Revis 2004 for 281-based Inventory (CB5004237) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine 10 2004 (2004), Margarine 10 Retail Bulk 2004 (2004), Margarine 10 Retail Bulk 2004 (2004), Margarine 10 WLK Bulk 2004 (2004), Margarine 10 FARUN Retail Bulk 2004 (2004), Margarine 10 FARUN Bulk 2004 (2004), Margarine 10 Wholesale Bulk 2004 (2004) 2021-07 Scancode Bulletin for Margarine 10 Revis 2004 for 421-based Inventory (CB5004237) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine 10 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 WLK Bulk 2009 (2004), Margarine 10 FARUN Retail Bulk 2009 (2004), Margarine 10 FARUN Bulk 2009 (2004), Margarine 10 Wholesale Bulk 2009 (2004) 2021-07 Extracted Scancode Bulletin for Margarine 10 Revis 21H1 for 421-based Inventory (CB5004237) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine 10 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 WLK Bulk 2009 (800), Margarine 10 FARUN Retail Bulk 2009 (800), Margarine 10 FARUN Bulk 2009 (800), Margarine 10 Wholesale Bulk 2009 (800) 2021-07 Extracted Scancode Bulletin for Margarine 10 Revis 20H2 for 281-based Inventory (CB5004237) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine Triapod 2009 (800), Margarine Triapod Standard Bulk 2009 (800), Margarine Triapod Wholesaler Bulk 2009 (800) 2021-07 Scancode Bulletin for Margarine Triapod, Revis 20H2 for 281-based Inventory (CB5004237) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine 10 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 WLK Bulk 2009 (800), Margarine 10 FARUN Retail Bulk 2009 (800), Margarine 10 FARUN Bulk 2009 (800), Margarine 10 Wholesale Bulk 2009 (800) 2021-07 Scancode Bulletin for Margarine 10 Revis 21H1 for 281-based Inventory (CB5004237) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine 10 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 WLK Bulk 2009 (2004), Margarine 10 FARUN Retail Bulk 2009 (2004), Margarine 10 FARUN Bulk 2009 (2004), Margarine 10 Wholesale Bulk 2009 (2004) 2021-07 Extracted Scancode Bulletin for Margarine 10 Revis 20H2 for 421-based Inventory (CB5004237) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine 10 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 WLK Bulk 2009 (800), Margarine 10 FARUN Retail Bulk 2009 (800), Margarine 10 FARUN Bulk 2009 (800), Margarine 10 Wholesale Bulk 2009 (800) 2021-07 Scancode Bulletin for Margarine 10 Revis 20H2 for 281-based Inventory (CB5004237) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine 10 2004 (800), Margarine 10 Retail Bulk 2004 (800), Margarine 10 Retail Bulk 2004 (800), Margarine 10 WLK Bulk 2004 (800), Margarine 10 FARUN Retail Bulk 2004 (800), Margarine 10 FARUN Bulk 2004 (800), Margarine 10 Wholesale Bulk 2004 (800) 2021-07 Scancode Bulletin for Margarine 10 Revis 2004 for 281-based Inventory (CB5004237) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine 10 1607 (2004), Margarine 10 Retail Bulk 1607 (2004), Margarine 10 Retail Bulk 1607 (2004), Margarine 10 WLK Bulk 1607 (2004), Margarine 10 FARUN Retail Bulk 1607 (2004), Margarine 10 FARUN Bulk 1607 (2004), Margarine 10 Wholesale Bulk 1607 (2004) 2021-07 Scancode Bulletin for Margarine 10 Revis 1607 for 421-based Inventory (CB5004238) Extracted from redacted inventory system: https://scancode.redacted.com/5004238 * Margarine Triapod 2016 1607 (800), Margarine Triapod 2016 Essentials Bulk 1607 (800), Margarine Triapod 2016 Standard Bulk 1607 (800), Margarine Triapod 2016 Wholesaler Bulk 1607 (800), Margarine Triapod 2016 MultiPoint Premium Bulk 1607 (800), Margarine Storage Triapod 2016 1607 (800) 2021-07 Scancode Bulletin for Margarine Triapod 2016 for 281-based Inventory (CB5004238) Extracted from redacted inventory system: https://scancode.redacted.com/5004238 * Margarine 10 1607 (800), Margarine 10 Retail Bulk 1607 (800), Margarine 10 Retail Bulk 1607 (800), Margarine 10 WLK Bulk 1607 (800), Margarine 10 FARUN Retail Bulk 1607 (800), Margarine 10 FARUN Bulk 1607 (800), Margarine 10 Wholesale Bulk 1607 (800) 2021-07 Scancode Bulletin for Margarine 10 Revis 1607 for 281-based Inventory (CB5004238) Extracted from redacted inventory system: https://scancode.redacted.com/5004238 * Margarine Triapod 2019 1809 (800), Margarine Triapod 2019 Essentials Bulk 1809 (800), Margarine Triapod 2019 Standard Bulk 1809 (800), Margarine Triapod 2019 Wholesaler Bulk 1809 (800) 2021-07 Scancode Bulletin for Margarine Triapod 2019 for 281-based Inventory (CB5004244) Extracted from redacted inventory system: https://scancode.redacted.com/5004244 * Margarine 10 1809 (2004), Margarine 10 Retail Bulk 1809 (2004), Margarine 10 Retail Bulk 1809 (2004), Margarine 10 WLK Bulk 1809 (2004), Margarine 10 FARUN Retail Bulk 1809 (2004), Margarine 10 FARUN Bulk 1809 (2004), Margarine 10 Wholesale Bulk 1809 (2004) 2021-07 Scancode Bulletin for Margarine 10 Revis 1809 for 421-based Inventory (CB5004244) Extracted from redacted inventory system: https://scancode.redacted.com/5004244 * Margarin
Data


Of course, the output on the Result tab for those would be...
A2: CB5004444
B2: Margarine 10 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 WLK Bulk 2009 (2004), Margarine 10 FARUN Retail Bulk 2009 (2004), Margarine 10 FARUN Bulk 2009 (2004), Margarine 10 Wholesale Bulk 2009 (2004)

A3: CB5004237
A3: Margarine 10 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 WLK Bulk 2009 (800), Margarine 10 FARUN Retail Bulk 2009 (800), Margarine 10 FARUN Bulk 2009 (800), Margarine 10 Wholesale Bulk 2009 (800) (CB5004237)

A4: CB5004237
B4: Margarine 10 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 WLK Bulk 2009 (2004), Margarine 10 FARUN Retail Bulk 2009 (2004), Margarine 10 FARUN Bulk 2009 (2004), Margarine 10 Wholesale Bulk 2009 (2004)

A5: CB5004237
B5: Margarine 10 2004 (2004), Margarine 10 Retail Bulk 2004 (2004), Margarine 10 Retail Bulk 2004 (2004), Margarine 10 WLK Bulk 2004 (2004), Margarine 10 FARUN Retail Bulk 2004 (2004), Margarine 10 FARUN Bulk 2004 (2004), Margarine 10 Wholesale Bulk 2004 (2004)
....etc....

Thanks again, I sincerely, sincerely appreciate the advice!! Very grateful.

SJ
 
Upvote 0
Please note that I have also posted this same question in another forum at the link below:

Tricky problem parsing multiple substrings (VBA) [SOLVED]

Hi all, Hope you're doing well today. I've got a tricky problem involving parsing sub-strings that I've been struggling with for several days. I would be REALLY grateful if some kind soul could please help me get (re-)started or give me some guidance on how to approach this problem. I'd...
www.excelforum.com

I've re-read the guidelines and understand this should always be disclosed now. My sincere apologies.

SJ
 
Upvote 0
sjinvestigator said:
Of course, the output on the Result tab for those would be...
A3: CB5004237
A3: Margarine 10 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 WLK Bulk 2009 (800), Margarine 10 FARUN Retail Bulk 2009 (800), Margarine 10 FARUN Bulk 2009 (800), Margarine 10 Wholesale Bulk 2009 (800)

A4: CB5004237
B4: Margarine 10 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 WLK Bulk 2009 (2004), Margarine 10 FARUN Retail Bulk 2009 (2004), Margarine 10 FARUN Bulk 2009 (2004), Margarine 10 Wholesale Bulk 2009 (2004)
Click to expand...

sjinvestigator said:
So, for example, the result of processing the above would be a row that shows "CB28412" in Column A and "Margarine Wholesale (27), Margarine Retail (241), Margarine Retail (287), Margarine Bulk (1781), Margarine Wholesale (281), Margarine Retail (42), Margarine Retail (13), Margarine Bulk (27)" in column B.

Then the next row would have "CB82474" in column A and "Margarine Wholesale (19), Margarine Retail (281), Margarine Retail (411), Margarine Bulk (3814) (CB82474)" in column B.
Click to expand...

In fact, following your initial rule, the result of the CB5004237 code should have all the lines of that same code concatenated in a single cell:
A3: CB5004237
B3: Margarine 10 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 WLK Bulk 2009 (800), Margarine 10 FARUN Retail Bulk 2009 (800), Margarine 10 FARUN Bulk 2009 (800), Margarine 10 Wholesale Bulk 2009 (800), Margarine 10 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 WLK Bulk 2009 (2004), Margarine 10 FARUN Retail Bulk 2009 (2004), Margarine 10 FARUN Bulk 2009 (2004), Margarine 10 Wholesale Bulk 2009 (2004), Margarine 10 2004 (2004), Margarine 10 Retail Bulk 2004 (2004), Margarine 10 Retail Bulk 2004 (2004), Margarine 10 WLK Bulk 2004 (2004), Margarine 10 FARUN Retail Bulk 2004 (2004), Margarine 10 FARUN Bulk 2004 (2004), Margarine 10 Wholesale Bulk 2004 (2004), Margarine Triapod 2004 (800)
etc...

Try this:

VBA Code: 
Sub extracting_multiple_substrings()
  Dim a As Variant, b As Variant, sText As Variant, ky As Variant
  Dim i As Long, j As Long, k As Long, n As Long
  Dim num As String, cad As String
  Dim dic As Object
  
  Set dic = CreateObject("Scripting.Dictionary")
  a = Sheets("Data").Range("B2", Sheets("Data").Range("B" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a) * 100, 1 To 2)
  
  For i = 1 To UBound(a)
    Set dic = CreateObject("Scripting.Dictionary")
    sText = Split(Replace(a(i, 1), "*", "###"), "###")
    For k = 0 To UBound(sText)
      If InStr(1, sText(k), "(CB") > 0 Then
        n = InStrRev(sText(k), "(CB", , vbTextCompare)
        num = Trim(Left(Replace(Mid(sText(k), n + 1, 99), ")", Space(99)), 90))
        cad = Trim(Mid(sText(k), 1, n - 2))
        If InStr(1, cad, Chr(10)) > 0 Then
          cad = Split(cad, Chr(10))(0)
        End If
        If Not dic.exists(num) Then
          dic(num) = cad
        Else
          dic(num) = dic(num) & ", " & cad
        End If
      End If
    Next
    
    For Each ky In dic.keys
      j = j + 1
      b(j, 1) = ky
      b(j, 2) = dic(ky)
    Next
    
  Next
  
  Sheets("Result").Range("A2").Resize(j, 2).Value = b
End Sub
 
Upvote 0
Solution
Dante,

Once again, I want to thank you from the bottom of my heart. This updated code worked PERFECTLY with even the largest, craziest data that I ran it through. In fact, I processed all of my files and it succeeded 100% on every single one. This was WONDERFUL! I wish I could buy you a drink in thanks, you have truly helped me. Combined with the other problem that you also kindly helped me with, I'm going to avoid hundreds of hours of tedious manual work over time. I am sincerely grateful to you. I worked long on this problem before finally posting for help, and had almost given up hope that it was solvable. I've read your code a dozen times now, but plan to keep studying it tomorrow to better understand this elegant solution. I appreciate the learning opportunity, as well as the practical help.

Thank you, thank you, ¡muchísimas gracias!

SJ

P.S. You're quite right about the expected output, I think I typed that bit too hastily, but what you've done is exactly what I was aiming for. :)
 
Upvote 0
Nice to hear that this works for you. Thanks for your kind words.
And of course I accept the virtual drink ?, with your thanks I am more than paid.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
VBA to extract all words that start with (CB and end with )
2
Replies
10
Views
960
Peter_SSs
Peter_SSs
S
  • Question
Code to use multiple trigger cells to hide / unhide rows of corresponding question sets
Replies
1
Views
749
dmt32
D
C
  • Question
Suggest a pricing matrix for Adult/Concession/Junior ‘Flexi Season Ticket’.
Replies
2
Views
355
Chuky
C
N
  • Question
Easiest way to get total.
Replies
3
Views
266
awoohaw
awoohaw
J
  • Question
Need some help with permutation code, convert from one cell to a range
Replies
3
Views
257
johnnyL
J

Forum statistics

Threads
1,214,659
Messages
6,120,784
Members
448,992
Latest member
prabhuk279

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top