using regexreplace in sheets to join elements with a comma

user_11713243 0
Viewed 45

I want to join with a comma several words than can be found in a cell by joining them with a comma.

For example, from a cell that contains :

i want to use sheets to create events in calendar through gmail.

I want this output : sheets, gmail, calendar

I tried a regexreplace like this:

=ArrayFormula(IFERROR(REGEXREPLACE(LOWER(A2:A);"(.)?(sheets|calendar|gmail)?";"$2, "))))"

But I get various results like this : , , , , sheets, , , , , , , , , , calendar, , , , , , , , , , , sheets, , , , , gmail, , , , , , , , , , , , gmail, , , calendar, , , , , , , , , , ,

I would like to know how to clean it like this: sheets, calendar sheets, gmail gmail, calendar

to get rid of the useless commas before in between and after so far, using a more complex formula, i can get this :

=ArrayFormula(IFERROR(REGEXREPLACE(REGEXREPLACE(MINUSCULE(A2:A);"(.)?(sheets|calendar|gmail)";"$2, ");"(, )+";", ")))

Still, the result is not perfect, since i get this : , sheets, calendar, , sheets, gmail, , gmail, calendar,

And I am using twice the regexreplace formula. By the way, i could do it using join, or textjoin, but it is not working with arrayformula for a whole column, so it would not be appropriate.

I think this is possible using () to capture elements, but I know some are real experts when it comes to this regex!

Do you have an idea ?

google-sheets regex
1 Answers

You may use

=ARRAYFORMULA(REGEXREPLACE(TRIM(REGEXREPLACE(A2:A, "(?si)(sheets|calendar|gmail)?.?", "$1 ")), "\s+", ","))

NOTES:

  • REGEXREPLACE(A2:A, "(?si)(sheets|calendar|gmail)?.?", "$1 ") - this will replace each char that is not a starting point of sheets, calendar and gmail substrings with a space, and will put back these char sequences if they are found
  • TRIM(...) will strip leading/trailing whitespace from the result of the above REGEXREPLACE
  • REGEXREPLACE(..., "\s+", ",") will replace chunks of 1+ whitespace chars with a single comma.
user_38329700
Related