The Remove Characters tool from Ultimate Suite for Excel helps you remove custom characters and character sets in Excel by position or delete all their occurrences in the selected cells. It's also possible to enter and remove a substring from your range.
Before you start
We care about your data. The add-in will back up your worksheet if you select the corresponding option.
How to remove characters or substrings
On the Ablebits Data tab, in the Text group, click Remove > Remove Characters:
You will see the Remove Characters pane with the options available:
- Select the cells that contain the values you want to delete. You will see the range address right in this field.
- Click the Expand selection icon to automatically select the entire table.
- Choose the option that meets your needs:
- Remove custom characters will delete the characters you specify. To delete several symbols, enter each of them into the Remove custom characters field and the add-in will delete all their instances in the selected cells.
- Remove character sets. There are several sets of symbols you can pick from the dropdown list:
- Non-printing characters - delete all non-printing characters like line breaks, the first 32 non-printing characters in the 7-bit ASCII code (values 0 through 31), and additional non-printing characters (values 127, 129, 141, 143, 144, and 157).
- Text characters - remove all letters from your cells.
- Numeric characters - delete all digits from the range of interest.
- Symbols - remove from the cells the following symbols: mathematical, geometric, technical and currency symbols, letter-like symbols such as ?, 1, and ™.
- Punctuation marks - get rid of all punctuation marks in the selected range.
- Remove a substring. Delete any combination of characters, for example a word, from the selected cells.
- To perform case-sensitive search, check the Case-sensitive box.
- Select the Back up this worksheet option to have a safe copy of your data.
Click the Remove button and enjoy the results.
How to remove characters by position
Run the Remove by Position tool by clicking the Remove icon on the Ablebits Data tab, in the Text group:
You can see the add-in's pane with the following options:
- To remove characters by position, select the range in Excel that contains the values you want to delete.
- Click Expand selection to get the entire table selected automatically.
- Pick The first N characters to delete any number of characters at the beginning of cell contents in the selected range.
- Select The last N characters to remove any number of characters at the end of each cell contents in your range.
- If you select All characters before text, any values before the specified character or string in the range will be deleted.
- Selecting All characters after text will let you remove everything after the specified character or string in the selected cells.
- You can also Remove all substrings between value 1 and value 2. For this, enter both values into the corresponding boxes. If you select the Including delimiters option, the substring will be removed together with the values you entered. If you do not check it, the values will remain in the cells.
- To perform case-sensitive search, select the Case-sensitive checkbox.
- Select the Back up this worksheet option to keep the original data intact.
Click the Remove button to see the results.
Delete duplicate substrings
To learn how to remove duplicate text within Excel cells, please refer to the How to Remove Duplicate Substrings guide.