Multiple [BRACKETED TERMS] on multiple related rows to single comma seperated cell

Could not figure out how to phase the problem.
Have tried to come up with a straight formula to do this but it seems like VBA is needed. (happy if someone disproves that and has a forumla)

For simplicity sake, we will say we have a wks with Column A for "Reciept numbers" and Column B; has text with Bracketed [ITEMS] and ad lib descriptions

I need to get a comma seperated listing of ONLY what is in the [BRACKETS] in a single cell, Ideally in Proper format (First Letter Of Each Word Capitalized) but I can do that another way seperately if need be.

This has been parsed or exported from an alternat source so unfortunately it is not consistant in how many [TERMS] are in each [CELL]
So Row 3 might have [TWO] [BRACKETED PHRASES] and Row 4, 5, and 6 might have one each, say [FOUR] [FIVE] [SIX] with plenty other words around them...

but all are associated with a single reciept (listed in A3) I want C3 to output: Two, Bracketed Phrases, Four, Five, Six

I guess the easiest way is if I upload an example wks that shows what I'm trying to have happen.

Like I mentioned, I have tried coming up with formulas to do this. like:=MID(A1,FIND("[",A1)+1,FIND("]",A1)-FIND("[",A1)-1) and many variations on that theme,.. but they all fail to capture multiple [BRACKETED PHRASES],. and if they do they capture all the text between the begining of the first bracketed phrase and the end of the last bracketed phrase,

It just looks like this requires VBA, and sadly , I'm feeling out of my depth after a few woeful attempts.

The key is just getting the Bracketed terms comma seperated into a single cell. Technically if that is done, I can eliminate the Brackets if they are still there, and I can change the capitalization.. But if it all happens in the macro, even better.

Very late here, Mind scrambled.
Thank you to any who attempt to help.
Really,

WOW!! NICE Jindon!!,.. You really are a guru.
That works perfectly on my example!!

Two things :

1. Sadly, I failed to think clearly enough to include "alpha-numeric" Reciepts (Column A) as my real data has letters mixed in "2011NZ123"

2. this module requires a blank row before each new Column A "Reciept #" ; What would be required to make it so that there may, or may not be a blank row, or possibly multiple blank rows .. (or would it be easier to ask how to force a blank row before each new Reciept #?)

Thanks soo much already. This gives me something to work with,.. Now if only the data given to me was even remotely clean and consistant (it is not... much is parsed from PDFs, so the more robust and forgiving this module the better..

I need to see a sample workbook with possible difficult data situation and the result that you want.

By the way, I am in the process of cleaning all the "Ugly Data" now from about 500-1000 items. There are likely only another 800-2500 "Ugly" entries after that.
Just don't want you spending too much energy helping me on that aspect as it is rather above and beyond.. Would much rather just have the cleanest version of your
original solution that works with my data (alpha numeric Column A) and no blank rows before new Column A key... (or an easy way to systematically format it all so that there IS only
a single blank before all new Column A keys)

Thanks again.. If this works it will litterally save me many hours of manually inputting data from PDFs with potential errors due to typing.

sanitizedsample2.xlsx

That almost works, except the Formula is based on the word "Source" which is not always present. (I realize after the fact that the chunk I took and sanitized for you to sample DID all have the word "Source" at the begining of each set of data in Column B,.. but that is far from the standard (maybe 50%)

And if an ID DOESN'T have "source" as it's first word in the column B all its [Bracketed] items are listed in the item befor it.. so if 5 items don't include "source" as the first word, then all [Bracketed] items from all 5 items are listed with the last item that did have "source" .

So I either need the formula to be based on the Key ID (Column A number) or I need a way to insert something unique at the head of each Column B Data group that could be used instead of "source" without throwing off the corrolation from the Column A IDs and their Column B data.

I am reuploading a similar version of my last sanitized version, but this one with some of the Column B Terms groups not including "source" just in case I was unable to convey the issue effectively or using the correct terminology.

Thank you again for everything...

I think it could work if: There was a way to say: Look at column A, if there is a number there, then simply insert "cr#zyWo%d" at the very beging of the corrosponding Cell in Column B without affecting the rest of the text in that cell otherwise.

Then we change "Source" to "cr#zyWo%d" in the VBA, and I assume it would work.

What about the block of data at the bottom in earlier file?

Not anymore?

For the latest file
1) change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

2) change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Testing your fix now.. (have to proof against multiple data sources) but so far so good

As for the block of data in earlier file, I manually went through and cleaned those up over a couple hours.. Some was really jumbled and there was no way to get it cleaned up without understanding it. No system would know how to break it up rationally.

Fingers crossed. What times are you normally available. (It's 2:00 AM here, but I'll stick with it if these are your peak hours)

Thank you sooooo ooooo ooooo much....

That's good.

It's 6 PM here and I normally available in daytime. say 10 - 8 but depends on a day of course...

Regarding the irregular block of data, it will be much easier if you don't care about the position for the output.

But I think you do care....

Yes the possition must relate to the row where the Column A ID is.
This way I can hide Column B and create a report that shows CaseID and the system specificTerms associated with it.
That is the exact output required.


I'm ELATED! This is working EVERYWHERE!!!! I have to go to sleep now because my typing is causing errors, but I think this nails it...

Still plenty of work to do yet, but realize how much you have just helped.
Much Real World Karma is due you.

Arigato Jindon Sensei !
*bows*x2

You are welcome and thanks for the feedback.