Remove Duplicates from Comma-Delimited Strings in Google Sheets

GiGiKing·March 21, 2024·0 comments

Are you tired of dealing with duplicates in your comma-delimited strings in Google Sheets? Look no further! In this tutorial, we will explore various methods to remove duplicates and simplify your data effortlessly.

The Magic of Non-Array Formulas

Let’s start with a non-array formula that will do wonders for your comma-delimited strings. Simply insert the following formula in cell B2:

=ArrayFormula(textjoin(", ",true,unique(trim(split(A2,",")),true)))

Here’s how it works:

  1. The SPLIT function divides the text around the comma and places each fragment into a separate cell in the row.
  2. The TRIM function removes any white spaces.
  3. The UNIQUE function eliminates duplicates.
  4. The TEXTJOIN function combines the fragments back into a comma-delimited string.

Easy, right? But what if your delimiter is not a comma? No worries! Simply replace split(A2,",") with split(A2,"|") if your delimiter is a pipe.

The Array Formula Solution

Now, let’s take it a step further with an array formula that expands for each row. Brace yourself for two types of array formulas: an old-school approach and a new one based on Lambda functions.

Old School Approach (Coded before Lambda): ✖

If you’re up for a bit of a challenge, you can try the following array formula:

=ArrayFormula(transpose(split(textjoin(", ",1,IFERROR((if(row(indirect("A2:A"&counta(to_text(unique(filter(flatten(split(if(len(A2:A),row(A2:A),)&" "&trim(substitute(A2:A,",",","&row(A2:A)&" ")),",")),flatten(split(if(len(A2:A),row(A2:A),)&" "&trim(substitute(A2:A,",",","&row(A2:A)&" ")),","))<>""))))))-match(REGEXEXTRACT(to_text(unique(filter(flatten(split(if(len(A2:A),row(A2:A),)&" "&trim(substitute(A2:A,",",","&row(A2:A)&" ")),",")),flatten(split(if(len(A2:A),row(A2:A),)&" "&trim(substitute(A2:A,",",","&row(A2:A)&" ")),","))<>""))),"w+"),REGEXEXTRACT(to_text(unique(filter(flatten(split(if(len(A2:A),row(A2:A),)&" "&trim(substitute(A2:A,",",","&row(A2:A)&" ")),",")),flatten(split(if(len(A2:A),row(A2:A),)&" "&substitute(A2:A,", ",","&row(A2:A)&" "),","))<>""))),"w+"),0)=1,"|",)&REGEXREPLACE(to_text(unique(filter(flatten(split(if(len(A2:A),row(A2:A),)&" "&trim(substitute(A2:A,",",","&row(A2:A)&" ")),",")),flatten(split(if(len(A2:A),row(A2:A),)&" "&trim(substitute(A2:A,",",","&row(A2:A)&" ")),","))<>""))),"^(w*W*){1}","")))),"|")))

This formula may look intimidating, but it does the trick. It removes duplicates from your comma-delimited strings effectively. However, note that it may consume more resources and encounter errors in larger data ranges.

New Approach (Lambda): ✔

Now, let’s introduce a clean and efficient solution using the Lambda formula. Simply insert the following code in cell B2:

=map(A2:A,lambda(r,iferror(ArrayFormula(textjoin(", ",true,unique(trim(split(r,",")),true))))))

Unlike the non-array formula, you don’t need to waste time copy-pasting this formula down the column. It automatically spills down if there are blank cells below B2. With the help of the MAP Lambda helper function, this formula does the job effortlessly.

And there you have it! Two powerful ways to remove duplicates from comma-delimited strings in Google Sheets. Say goodbye to cluttered data and hello to clean and organized spreadsheets.

For more expert tips and tricks, visit Crawlan.com. Happy sheeting!

GiGiKing

Related posts