This is a new feature and we can’t wait to hear your feedback. Is it useful for your tasks? Is it easy to use? Just let us know via website or email us.
Use with caution: beta features may contain some bugs and rough edges. To test the feature, take a free trial or update XLTools.
Delete text strings by their position in Excel cells or by character type
When you need to delete some text from all cells, you can use the standard find and replace functionality in Exсel, or use functions or VBA. However, it gets complicated and unproductive, if you need to process large spreadsheets and remove characters by their position in a cell, or by character type.
The Delete Characters feature helps remove characters from cells in seconds:
- Delete any custom substrings from cells
- Delete from the beginning, from the end, or from the middle of all cells
- Delete before or after specific text
- Delete all characters except numbers and/or letters
Before you begin, add Delete Characters to Excel
Delete Characters is one of the 20+ features within XLTools Add-in for Excel. Works in Excel 2019, 2016, 2013, 2010, desktop Office 365.
How to delete specific characters from all cells
Click the Data Cleaning button on XLTools ribbon Select Delete characters from the drop-down list A dialogue box will open.
Select the range where you want to delete characters.
Tip: click on any cell in a table, and the entire table will be automatically selected.Choose these characters Type the substring to search for and delete If necessary, check the box case sensitive.
![Select the range where to find and delete the substring]()
Click OK Done, the text string is deleted from all cells where it was found.
![See result: the specified substring is deleted from all cells]()
Select 
How to delete characters from the beginning of cells
Click the Data Cleaning button on XLTools ribbon Select Delete characters from the drop-down list A dialogue box will open.
Select the range where you want to delete characters.
Tip: click on any cell in a table, and the entire table will be automatically selected.Choose the position in a cell: the first N characters Specify the number of characters from the beginning that you want to delete.
![Select the range where to delete characters]()
Click OK Done, the characters are deleted from the beginning of each cell.
![See result: the substring is deleted from the beginning of all cells]()
How to delete characters from the end of cells
Click the Data Cleaning button on XLTools ribbon Select Delete characters from the drop-down list A dialogue box will open.
Select the range where you want to delete characters.
Tip: click on any cell in a table, and the entire table will be automatically selected.Choose the position in a cell: the last N characters Specify the number of characters from the end that you want to delete.
![Select the range where to delete characters]()
Click OK Done, the characters are deleted from the end of each cell.
![See result: the substring is deleted from the end of all cells]()
How to delete characters from the middle of cells
Click the Data Cleaning button on XLTools ribbon Select Delete characters from the drop-down list A dialogue box will open.
Select the range where you want to delete characters.
Tip: click on any cell in a table, and the entire table will be automatically selected.Choose the position in a cell: skip N and delete N characters:
- Specify the number of characters from the beginning you want to skip
- Specify the number of characters you want to delete
![Select the range where to delete characters]()
Click OK Done, the characters are deleted from the middle of each cell.
![See result: the substring is deleted from the middle of all cells]()
How to delete characters before or after specific text in cells
Click the Data Cleaning button on XLTools ribbon Select Delete characters from the drop-down list A dialogue box will open.
Select the range where you want to delete characters.
Tip: click on any cell in a table, and the entire table will be automatically selected.Choose the position in a cell: all before text or all after text Type the text string to search for (case sensitive).
![Select the range where to find and delete characters]()
Click OK Done, all characters before or after the specified text are deleted from those cells, where it was found.
![See result: all characters before or after specific text are deleted]()
How to delete characters from cells by character type
Click the Data Cleaning button on XLTools ribbon Select Delete characters from the drop-down list A dialogue box will open.
Select the range where you want to delete characters.
Tip: click on any cell in a table, and the entire table will be automatically selected.Choose all characters except Choose the character type you want to remain:
- numbers
- letters
- letters and numbers
![Select the range where to delete characters]()
Click OK Done, all unwanted character types were removed from the cells.
![See result: all characters except for numbers or letters are deleted from cells]()
Set options for deleting characters from cells
By default, the Delete Characters feature does not process cells that contain formulas, not to interfere with calculations. You can specify additional data processing options:
Check the Skip non-text cells option
Select this option, if you want to process only cells with text. All cells that have number, date, currency or other format will be ignored.
Check the Skip header row option
Select this option, if you want to ignore the first row with the table header.
