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.
You can make your own copy of the spreadsheet above using the link attached below.
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
andREGEXEXTRACT
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 ourREGEXEXTRACT
function with anARRAYFORMULA
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 theLET
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!