If you’re a Google Sheets user, you may have encountered a situation where you need to find duplicates within new lines inside cells. Luckily, there’s a simple and effective way to do this using conditional formatting.
How to Find Duplicates in New Lines Inside Cells in a Column
To highlight duplicates in new lines inside cells in a column, follow these steps:
- Select the cell range you want to check for duplicates.
- Go to the “Format” menu and choose “Conditional formatting.”
- In the “Format cells if” dropdown menu, select “Custom formula is.”
- Enter the following formula in the “Value or formula” field:
=ArrayFormula(not(regexmatch(trim(regexextract(A1&"","n.*")),"^"&textjoin("$|^",1,unique(trim(ifna(regexextract($A$1:$A1&"","n.*"))),false,true))&"$")))
- Replace “A1” and “$A$1:$A1” with the first cell in your selected range.
- Choose the formatting style you want to apply to the duplicates.
- Click “Done” to apply the formatting.
This formula will highlight cells in the column wherever duplicate values appear in new lines within each cell.
Analyzing the Rule: Extracting Second Line Values in Cells
Let’s break down the formula and understand how it works:
- Part 1: Extracting the value in the second line from each cell using the
REGEXEXTRACTfunction. - Part 2: Creating a regular expression to match the distinct values in new lines up to the current row using the
UNIQUEandIFNAfunctions. - Part 2a: Combining the extracted values from part 2 and forming a regular expression using the
TEXTJOINfunction. - Part 3: Comparing the extracted value in part 1 with the distinct values in part 2a to determine if there are duplicates.
By following these steps, you can easily find duplicates within new lines inside cells in a column in Google Sheets.
Finding Duplicates in New Lines Inside Cells Across Rows
What if you need to find duplicates in new lines across rows in a table? The formula mentioned above won’t work in this case. Here’s the modified formula to handle this scenario:
=ArrayFormula(not(regexmatch(trim(regexextract(A1,"n.*")),"^"&textjoin("$|^",1,unique(trim(ifna(regexextract($A1:$D1,"n.*"))),true,true))&"$")))
This formula uses the same logic as before but takes into account multiple rows and columns. Select the range you want to check for duplicates and apply this formula to find duplicates within new lines across rows.
Resources
To learn more about manipulating new lines within cells in Google Sheets, check out these useful resources:
- Start New Lines Within a Cell in Google Sheets – Desktop and Mobile
- How to Move New Lines in a Cell to Columns in Google Sheets
- Remove Whitespaces at the Beginning of a Newline in Google Sheets
- Extract Every Nth Line from Multi-Line Cells in Google Sheets
Now you have the techniques to find and highlight duplicates within new lines inside cells in Google Sheets. Happy spreadsheeting!
