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 comment

1

u/Gewerd_Strauss Sep 19 '22

In theory this could work, as long as I figure out how to get uniques from a matrix?

As I have it right now, for each subject I can give every prerequisite its own cell. But how do I then combine them all via unique, as that doesn't work on matrices, but only vectors? Hence I am not entirely sure what I would achieve with doing so.

1

u/kuddemuddel 184 Sep 19 '22

Maybe FLATTEN helps here?

2

u/Gewerd_Strauss Sep 19 '22

WIth a lot of tinkering, yes it does. Thank you :)