Replace duplicate codes in string in a single cell

[davidswatton]

Hi guys,

New to this forum as I have a problem I can't solve. I have a worksheet comprising a single column that has data in it comprising codes separated by commas. The codes might be repeated and need to be replaced if they repeat with the number of times they repeat.

Below is a potential entry:

aa,bb,aa,cc,dd,aa

I need to replace this entry with:

aa*3,bb,cc,dd

Because the code aa appears 3 times.

Another example might be: aa,aa,bb,bb,cc,cc,aa which would need to result in aa*3,bb*2,cc*2

Now, for each entry in the worksheet I won't know which codes repeat in a given cell or how many times they repeat and they are not guaranteed to be in the same order. So, for example:

aa,aa,bb,cc,aa,dd would also need to be transposed to aa*3,bb,cc,dd

The codes aa,bb,cc etc are all alphanumeric and the same length.

I'm struggling so any help gratefully accepted.

Regards.

[Olly]

You could use Power Query - something like:

Please Login or Register  to view this content.

Worked example file: https://excel.solutions/xlf_1277667/

[davidswatton]

That's brilliant - many thanks.

D.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.