How to Find Duplicates in New Lines Inside Cells in Google Sheets

This guide will explain how to find duplicates in new lines inside cells in Google Sheets.

A Real Example of Finding Duplicates in New Lines Inside Cells
How to Find Duplicates in New Lines Inside Cells in Google Sheets

When working with Google Sheets data, you may come across cells with multiple lines. Google Sheets allows cells to have multiple lines through the addition of a line break.

multi-line cell

The user can insert a line break by pressing Alt + Enter (Windows) or Cmd + Enter (Mac). However, it may be difficult to look up any individual line in a multi-line cell. For example, you may want to compare the second line of two cells to see if they are equal.

We can return the value in the second line through the REGEXEXTRACT function in Google Sheets. Afterward, we can use functions like COUNTIF to determine if the value appears in other multi-line cells.

In this guide, we will provide a step-by-step tutorial on how to find duplicates in new lines inside cells in Google Sheets.

We will cover how to extract a new line from a multi-line cell and how to determine if the extracted value is a duplicate. We will also provide an example of how to perform both these steps using a single custom formula.

Let’s dive right in!

A Real Example of Finding Duplicates in New Lines Inside Cells

Let’s explore a simple example where we are tasked to find duplicates in new lines.

In the table below, we have a dataset of six cells. Each cell has a student’s name in the first line and their corresponding college major in the second line.

sample data

We want to determine what cells have the same college major.

use REGEXEXTRACT to extract text after newline

We can use the TRIM and REGEXEXTRACT functions to return the college major from the multi-line cell.

In the above example, we extracted the target value using the following formula:

=TRIM(REGEXEXTRACT(A2&"","\n.*"))

The first argument of REGEXEXTRACT should be the text value you will extract from. In this example, we’ll use the value in cell A2.

The second argument of REGEXEXTRACT indicates the regular expression to use to extract text from the first argument. In our example, we’ll use the pattern “\n.*”. This pattern matches a newline character (\n) followed by any number of characters (.*).

use countif to find duplicates in new lines in google sheets

After extracting the text from our multi-line cells, we can use the COUNTIF function to determine if the college major appears in a prior entry. If the COUNTIF function returns a value greater than 1, we can conclude there is at least one duplicate in the given range.

We’ll use the formula =COUNTIF($B$2:$B$7,B2)>1 to return either TRUE or FALSE, which will also indicate whether a duplicate exists in the range.

alternate logic for duplicates

We can convert the first argument of COUNTIF to an absolute reference to flag all duplicate values, including the first instance. In the image above, both A4 and A7 are marked as a duplicate.

using LET function

We can also perform the steps mentioned earlier as a single formula:

=LET(newline,ARRAYFORMULA(TRIM(REGEXEXTRACT(A11:A16&"","\n.*"))),ARRAYFORMULA(COUNTIF(newline,newline)>1))

The LET function allows us to store a copy of the extracted college major values under the name “newline”. We’ll then use the newline reference multiple times for our COUNTIF formula in the third argument.

You can make your own copy of the spreadsheet above using the link attached below.

Make a copy of our sample spreadsheet

Use our sample spreadsheet to compare the different methods used to find duplicates in new lines inside cells in a Google Sheets spreadsheet.

If you’re ready to try finding duplicates in multi-line cells, head over to the next section to read our step-by-step breakdown on how to do it!

How to Find Duplicates in New Lines Inside Cells in Google Sheets

Here’s our step-by-step guide on how to detect duplicate values in new lines inside cells in Google Sheets.

First, create a new column next to your dataset. We’ll use this column to store the value found after the newline character.
We’ll use the TRIM and REGEXEXTRACT functions to return the value after the newline character.
The TRIM function will remove any leading or trailing whitespace characters from our result.
Use the AutoFill tool to extract the target line from the remaining multi-line cells.
Next, we’ll create one more column where we’ll place the result of our COUNTIF function. This function will help determine if the extracted value is a duplicate.
Use the AutoFill tool to complete the rest of the cells.
In the image above, we can see that the 2nd line in cell A7 is a duplicate value.
We can convert our range to an absolute reference if we must also flag the first instance of our duplicated value.
We can use the LET function to extract all new lines in a range of cells at the same time. We must wrap our REGEXEXTRACT function with an ARRAYFORMULA and replace our single cell reference to a cell range reference.
After assigning the result to a name, we can use these values in our COUNTIF function for the third argument of the LET function.

That’s all the steps you need to know to find duplicates in new lines inside cells in Google Sheets!

After reading our detailed guide, you should have a clear idea of how to find duplicates in new lines inside cells. You can use our tips to start looking for duplicates within multi-line cells in your spreadsheet.

This guide is just one of the many methods you can use to find duplicates in Google Sheets. For more tips, you can read our post on how to count duplicates.

That’s all for this guide! If you’re still looking to learn more about Google Sheets, be sure to check out our library of spreadsheet resources, tips, and tricks!