Tricky problem parsing multiple substrings (VBA)

**sjinvestigator** · 08-11-2021, 08:32 PM

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 spreadsheet for an example of the data I am working with and expected results I want to get after running the macro on the Results 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 example file 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

**jindon** · 08-12-2021, 02:07 AM

Based on your explanation.
try

Please Login or Register  to view this content.

**Rick Rothstein** · 08-12-2021, 02:37 AM

This question has been cross-posted at the MrExcel forum.

**jindon** · 08-12-2021, 02:50 AM

Outputs are totally different, so not the same questions.

**Rick Rothstein** · 08-12-2021, 08:30 AM

Originally Posted by jindon

Outputs are totally different, so not the same questions.

Isn't this the same question where he has acknowledged getting the answer for it? It looks like the same requested output to me.
https://www.mrexcel.com/board/thread...rings.1178940/

**jindon** · 08-12-2021, 08:41 AM

Ahhh, I thought it was

https://www.mrexcel.com/board/thread...-with.1178943/

**sjinvestigator** · 08-12-2021, 08:51 AM

Yes, I acknowledge I cross-posted this. It wasn't intentional, I started here and after I submitted it, my post didn't appear. So, I thought it did not go through and went to the other forum. On that forum, I posted this same question as well as a totally different one that used a similar data set. When they alerted me to the problem over there, I re-read the guidelines on both and acknowledged. Strangely, I tried to reply here earlier today to acknowledge it on this thread but it still hasn't appeared. So, I'm trying again. I tried to provide the link but I'm not yet allowed to post links.

I'm a newbie and I sincerely apologize. It wasn't my intention to cause any trouble or intentionally violate the rules. I'm not sure why I'm having trouble with my posts appearing here, but it's probably user error.

I want to take a moment to thank EVERYONE who has taken the time out of their day to comment and assist. You guys are the best, and I am grateful.

**jindon** · 08-12-2021, 08:53 AM

Is my code working or not?

**sjinvestigator** · 08-12-2021, 09:38 PM

Hi Jindon,

I'm so sorry for the delay in responding to you. An unexpected emergency came up at work and I was unable to actually test the code until now. Yes, I confirm that your code worked perfectly using the original dataset that I provided. Thank you VERY, VERY much for taking the time out of your day to look at my problem. I am grateful for your support and wish I'd been able to respond sooner.

I'm going to mark as solved as your code did work against my original dataset. However, in full disclosure, when I tried it against an extremely large dataset, I got a subscript out of range error on this line:

myCode = Replace(Split(y(UBound(y)), ") (")(1), ")", "")

Though this never happened with any of the smaller sets So, it worked for 99% of my use cases, including the sample I provided. It's not your fault I didn't provide even larger data as an example. Fortunately, I received another reply on the other forum that worked properly even with extremely large data, so my problem is completely solved. I feel terrible about accidentally crossposting here and will never make that mistake again. Nevertheless, I want you to know that I learned something new directly from your code. You utilized the "Filter" function in a way that was very new to me. I spent a long time reading the help after reviewing your code and learned a lot. I also enjoyed that cool button you used and intend to utilize something similar in the future. Your effort was not in vain, and I am sincerely grateful for your help.

Arigatou gozaimasu!!!!

I hope you have a wonderful day.

SJ

**jindon** · 08-12-2021, 09:47 PM

That's not the matter of the number of data, but the data itself.

You should have the hidden data that don't follow what you uploaded, so impossible to even guess.

**sjinvestigator** · 08-12-2021, 09:58 PM

Originally Posted by jindon

That's not the matter of the number of data, but the data itself.

You should have the hidden data that don't follow what you uploaded, so impossible to even guess.

You're absolutely right, it's certainly not your fault. I can't expect you to have guessed! The file I uploaded is representative of 99% of the files I needed to run this macro on. However, I do have a FEW files with some super long and complex entries, though fortunately just a small number. It was MY fault for not including an example of the really big ones in my initial post. If you are interested, I'm uploading a file with the longer text that it failed on. If you delete the last example (April) your code works perfectly, but for some reason fails with the last one included. But as I said, I got another solution in the other forum that solved that bit, so you don't have to spend any more time on it. Again, though, I don't want to waste your time, I'm simply uploading it in case you're curious. I'm just grateful you took the time to look at it and help. Sincerely!

Thanks,

SJ

**jindon** · 08-12-2021, 11:28 PM

Change to

Please Login or Register  to view this content.

**sjinvestigator** · 08-13-2021, 07:50 PM

Well done, Jindon! This is a different approach than the previous code but it worked perfectly even on my really large files. I tested it on everything with no issues at all.

I'm impressed! Thank you very much, that was very kind of you to share your knowledge, and I appreciate it.

Arigatou gozaimasu!

SJ

Tricky problem parsing multiple substrings (VBA)

LinkBack

Thread Tools

Rate This Thread

Display

Tricky problem parsing multiple substrings (VBA)

Re: Tricky problem parsing multiple substrings (VBA)

Re: Tricky problem parsing multiple substrings (VBA)

Re: Tricky problem parsing multiple substrings (VBA)

Re: Tricky problem parsing multiple substrings (VBA)

Re: Tricky problem parsing multiple substrings (VBA)

Re: Tricky problem parsing multiple substrings (VBA)

Re: Tricky problem parsing multiple substrings (VBA)

Re: Tricky problem parsing multiple substrings (VBA)

Re: [SOLVED] Tricky problem parsing multiple substrings (VBA)

Re: [SOLVED] Tricky problem parsing multiple substrings (VBA)

Re: [SOLVED] Tricky problem parsing multiple substrings (VBA)

Re: [SOLVED] Tricky problem parsing multiple substrings (VBA)

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Color substrings if between 2 substrings

Replace multiple substrings in string

Returning multiple txt Substrings and using them as search term

[SOLVED] VBA to Find Multiple Substrings Within a String

Look for multiple substrings in a sring

Changing multiple substrings within a string

Tricky Problem

Bookmarks

Bookmarks

Posting Permissions