How to Remove Duplicate Substrings

With Ultimate Suite for Excel

Sometimes, if you combined several Excel sheets together, you may get multiple duplicates that need to be deleted. To remove duplicates from Excel sheets and tables, you can use the Duplicate Remover tool.

However, duplicate text may appear not only in columns and rows but within cells as well. For such cases, we've developed a handy helper that deletes duplicate values in Excel cells. Find out how to use the Remove Duplicate Substrings utility in this guide.

Before you start

  • The Remove Duplicate Substrings utility supposes that duplicate values in your cells are divided by certain delimiters that you will indicate while working with the add-in. If the tool finds duplicate substrings, it clears them together with delimiters. For example, if in a cell you have AAA;BBB;AAA;CCC;BBB and pick Semicolon as a delimiter:
    Remove Duplicate Substrings example.
    You'll get the following result:
    Remove Duplicate Substrings example result.
  • While working with the add-in, you can't use Excel's Undo option. We suggest you always create backup copies of your worksheets by keeping the Back up this worksheet option checked.

Run Remove Duplicate Substrings

To run the tool, go to the Ablebits Data tab and click on the small down arrow under the Duplicate Remover icon:

Start Remove Duplicate Substrings.

In the drop-down list, click on Remove Duplicate Substrings:
Start Remove Duplicate Substrings.

Alternatively, you can find the Remove Duplicate Substrings icon in the Remove drop-down list in the Text group:
Start Remove Duplicate Substrings.

How to remove duplicate substrings in Excel

Before clicking the Remove button, select the options:
Remove Duplicate Substrings pane.

  1. Select or enter the range with cells that may contain duplicate substrings.
  2. On the right, you can click on the Expand Range icon to expand the selection into the entire table or Select Range to collapse the add-in window and select the range.
  3. Check the My table has 1 header row box if your table has a header that should not be included in the duplicate search. You can click on the '1 header row' blue phrase and enter the number of header rows that must be excluded from the search:
    Click 1 header row.
  4. The tool supposes that duplicate substrings in your cells are divided by certain characters–delimiters. You can tick one or several offered delimiters.
  5. You can also check the Custom option and enter the character by which duplicate values in your cells are divided:
    Tick Custom and enter the delimiter.

    Tip. Into the Custom field, you can enter several delimiters that will be processes as one.
  6. If you have several delimiters following each other continuously and you want the tool to treat them as one delimiter (and, if duplicate substrings are found, remove all these delimiters), opt for the Treat consecutive delimiters as one option.
  7. If you leave the Case-sensitive option checked, the tool will search for duplicate substrings of the same case and will not identify 'AAA' and 'aaa' as duplicates. If you uncheck this option, 'AAA' and 'aaa' will be identified as duplicates.
  8. If you keep this box checked, a backup copy of your sheet will be automatically created in your workbook. It will have the same name as the initial sheet with a hashtag before it. If your sheet is named "Sheet", the backup copy will be named "#Sheet(1)".