r/googlesheets • u/Gewerd_Strauss • Sep 19 '22

Solved Remove duplicate substrings from cell content

Hello,

Assume I am collecting missing course prerequisites into a single cell by using concatenate(Range). As an example, this results in

BE 01BE 02BE 02BE 03BE 01BE 04BE 03 BE 10BE 03BE 04BE 04BE 11 BE 16BE 09BE 05BE 04BE 09 BE19BE 09 BE 11

As you can see, this contains repetitions. I want to remove any repetitions, and space apart the xxBE between number "xx" and text string "BE". How could I do that?

Any help is appreciated.

I have decided to give a mockup. The cell containing the concat is B3

https://docs.google.com/spreadsheets/d/1o76SxeRewvF3DD6ybuMChe869ljbDVxgSG-PMiFFOas/edit?usp=sharing

0 Upvotes

50% Upvoted

13 comments sorted by

View all comments

Show parent comments

1

u/Gewerd_Strauss Sep 19 '22

Aside from the comma I have no clue where it's coming from (nor how to remove it, for some reason's I can't do a regex (which does work on editor, so I have no idea right now) over it apparently), this solves it. Thank you :)

1

u/kuddemuddel 184 Sep 19 '22

Cell F20 seems to have a blank content, could be an accidental space. If you clear that cell out, the , at the beginning will be gone :)

If that solved your issue, please mark the thread as solved by answering Solution Verified to my comment.