With Ultimate Suite for Excel

How to Remove Duplicate Substrings

When combining several Excel sheets, 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, 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:
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 1 header row 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 values in your cells are delimited:
    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)".

Click Remove to eliminate duplicate substrings in your dataset.

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.