Thank you for your time and help p45cal. I am always interested having different solutions, I didn't use Power Query before. My original data source has more than 5000 records and string data is quite complex ones usually like in cell F2 in Sample.

However I wonder how this solution works, where can I find the details of this Power Query please?

Many Thanks
B.


Quote Originally Posted by p45cal View Post
I realise you asked for VBA which Paul has successfully provided, however, in the attached there's a Power Query solution.
In sheet Sample (2), your source data in a proper Excel table at cell A1
A Power Query table at cell I1.
If the data in the source table changes you can refresh the result table by right-clicking it and choosing Refresh (like you do with pivot tables.

I hope columns I to M appear as you want them to.
You'll see in this result table that column N (headed String) also repeats the string for every row in the results, which isn't too bad for this table, but if it were to be applied to to long text data as in cell F2 of the Sample sheet, it could become tiresome.

So on sheet Sample, in the Power Query results table at cell M1, I've used the same technique but removed the duplicates.
The source table for this is your data at cells D1:F2
Thankfully, this shows the same results as Paul's code which is in columns H to K. I hope columns M:N are as you want them.

If this is of interest, come back and I'll explain some more!