How to Remove Duplicate Substrings

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:
  
  You'll get the following 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, the Dedupe group, and click on the small down arrow under the Duplicate Remover icon. In the drop-down list, click on Remove Duplicate Substrings:

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

How to remove duplicate substrings in Excel

Before clicking the Remove button, select the options:

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 1 header row and enter the number of header rows that must be excluded from the search:
   
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 values in your cells are delimited:
   
   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)".

Click Remove to eliminate duplicate substrings in your dataset.