# 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.

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.

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.

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

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 (.*).

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.

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.

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.

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.

1. First, create a new column next to your dataset. We’ll use this column to store the value found after the newline character.
2. 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.
3. Use the AutoFill tool to extract the target line from the remaining multi-line cells.
4. 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.
5. 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.
6. We can convert our range to an absolute reference if we must also flag the first instance of our duplicated value.
7. 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.
8. 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!

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive early access to new content.

## How To Remove Duplicates in Google Sheets

In this article, we’ll see how to remove duplicates in Google Sheets. Consider you have a massive amount…

## How To Use IMCOS Function in Google Sheets

The IMCOS function in Google Sheets is useful when you would like to retrieve the cosine of a…

## How to Use the MMULT Function in Google Sheets

The MMULT function in Google Sheets is short for “matrix multiplication function” and it automates row-to-column multiplication for…