On Wed, 26 Oct 2011 16:11:57 -0700 (PDT), geniusideas wrote:

Sorry Guy,

The above example both OK but for my actual data was not accurate. my
actual data have more spaces in between as below:

Before
AA AAAA (BB BBBB) (CC CCCC(DD)) | AA AAAA (EE EEEE) (FFFFFF) | GG GGGG
(EE EEEE) (HHHHHH)

After
AA AAAA
(BB BBBB)
(CC CCCC(DD))
(EE EEEE)
(FFFFFF)
GG GGGG
(HHHHHH)

Before is in one line and after become line by line and remove
duplicate

Sorry Guy. The question is not accurate before
Please help.
Thanks

It should not be any surprise that if you supply neither representative data, nor clear cut specifications, you will receive solutions which only work on the data you supply.

Is the data you have supplied now truly representative? Or are there other variations which you are keeping a secret?

To account for the new data you have supplied, in the solution I provided, you only need to change the regular expression pattern.


.Pattern = "\([^)]+\)+|(\w)(?:[\s\S](?=\1|\b))*"


Whether it will work on your "actual data" depends on how representative your examples are. You show strings which consist of the SAME letter repeated a varying number of times, sometimes with an included space; and sometimes surrounded by parentheses. If that does not describe your real data, then the routine will not work.

Also note that what I have supplied will only work on a single cell. To have this work on multiple cells, in addition to the looping, you also will need to clear out the collection each time. But maybe you only have a single cell to deal with.