Share via

How to find duplicate text strings?

Anonymous
2019-05-15T13:28:10+00:00

Hi,

I know I could use conditional formatting to find duplicate cells.

But how do I find duplicate text like 'A4' and 'A5' which has '**ZDTPVK'**in both cells?

Urgent.

Thanks in advance.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Andreas Killer 144K Reputation points Volunteer Moderator
    2019-05-15T14:13:18+00:00

    B2: =COUNTIF(A:A,"*"&A2&"*")

    and drag down. After that any value in B > 1 is a duplicate in A anywhere.

    Then you can e.g. filter column A and show all items that includes that string.

    Andreas.

    2 people found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 100.8K Reputation points Volunteer Moderator
    2019-05-16T00:10:33+00:00

    Hi,

    Using Data > Get & Transform, you may first Split the data into rows so that each entry appears in its own cell.  Then you may use conditional formatting or a Pivot Table.

    0 comments