How to Filter Cells that have Duplicate Text Strings in Excel

Sometimes, you may duplicate texts/words while working on an Excel Worksheet. In the case of a worksheet with a large dataset, it may pose a significant challenge to identify the duplicate strings manually. However, Microsoft Excel is equipped with numerous tools that can be used for duplicate Text strings. In this post, we shall discuss common and easy ways of filtering cells with duplicate Text strings.

Using Macros to filter cells with Duplicate Text Strings

Macros are Excel tool that allows users to use code to perform some operations on the Worksheet. Here are the steps to follow while using this method to filter duplicate cells:

1. Open the Excel application.

2. Open the Excel workbook that contains the Worksheet you want to check the duplicate.

3. Click on the Developer tab on the Ribbon, and then locate the Visual Basic button. Alternatively, press the Alt + F11 keys on your keyboard to open the Visual Basic feature.

4. In the Visual Basic screen, click the Insert tab on the Ribbon and select the Module button.

5. Type the following code in the empty module.

Function Duplicates(rng As Range) As String

Dim StringtoAnalyze As Variant

Dim i As Integer

Dim j As Integer

Const minWordLen As Integer = 4

StringtoAnalyze = Split(UCase(rng.Value), " ")

For i = UBound(StringtoAnalyze) To 0 Step -1

If Len(StringtoAnalyze(i)) < minWordLen Then GoTo SkipA

For j = 0 To i – 1

If StringtoAnalyze(j) = StringtoAnalyze(i) Then

Duplicates = "TRUE"

GoTo SkipB

End If

Next j

SkipA:

Next i

Duplicates = "FALSE"

SkipB:

End Function

6. Press the F5 button to run the code. A Macros dialogue box will open. In the dialogue box, type the name of your macros. Then, click the Create button.

7. Close the Visual Basic screen.

8. Type the Equal sign followed by the variable you assigned in the VBA code. For example, in our case, we assigned Duplicates.

9. Select the cell index of the cell you want to check duplicates.

10. Finally, drag the formula to other cells in the Worksheet.

Using Visual Basic to highlight Duplicate Text Strings

Steps:

1. Open the Excel workbook that contains the Worksheet you want to check the duplicate.

2. Click on the Developer tab on the Ribbon, and then locate the Visual Basic button.

3. In the Visual Basic screen, click the Insert tab on the Ribbon and select the Module button.

4. Type the following code in the empty module.

Public Sub HighlightDupesCaseInsensitive()

   Dim Cell As Range

    Dim Delimiter As String

    Delimiter = InputBox("Enter the delimiter that separates values in a cell", "Delimiter", ", ")

    For Each Cell In Application.Selection

        Call HighlightDupeWordsInCell(Cell, Delimiter, False)

    Next

End Sub

Sub HighlightDupeWordsInCell(Cell As Range, Optional Delimiter As String = " ", Optional CaseSensitive As Boolean = True)

    Dim text As String

    Dim words() As String

    Dim word As String

    Dim wordIndex, matchCount, positionInText As Integer

    If CaseSensitive Then

        words = Split(Cell.Value, Delimiter)

    Else

        words = Split(LCase(Cell.Value), Delimiter)

    End If

    For wordIndex = LBound(words) To UBound(words) – 1

        word = words(wordIndex)

        matchCount = 0

        For nextWordIndex = wordIndex + 1 To UBound(words)

            If word = words(nextWordIndex) Then

                matchCount = matchCount + 1

            End If

        Next nextWordIndex

        If matchCount > 0 Then

            text = ""

            For Index = LBound(words) To UBound(words)

                text = text & words(Index)

                If (words(Index) = word) Then

                    Cell.Characters(Len(text) – Len(word) + 1, Len(word)).Font.Color = vbRed

                End If

                text = text & Delimiter

            Next

        End If

    Next wordIndex

End Sub

5. Press the F5 button to run the code.

6. Finally, close the Visual Basic screen.