Beta

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 or . Use with caution: beta features may contain some bugs and rough edges. To test the feature, take a . or Use with caution: beta features may contain some bugs and rough edges. To test the feature, take a XLTools.

Delete text strings by their position in Excel cells or by character type

How to delete substrings from Excel cells with XLTools add-in

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.

Get started with XLTools ribbon
Download XLTools for Excel
 – free trial gives you 14 days of full access to all features.

How to delete specific characters from all cells

  1. Click the Data Cleaning button on XLTools ribbon Select Delete characters from the drop-down list A dialogue box will open.

    2. 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.
  2. 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
  3. 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

How to delete characters from the beginning of cells

  1. Click the Data Cleaning button on XLTools ribbon Select Delete characters from the drop-down list A dialogue box will open.

    2. 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.
  2. 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
  3. 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

  1. Click the Data Cleaning button on XLTools ribbon Select Delete characters from the drop-down list A dialogue box will open.

    2. 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.
  2. 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
  3. 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

  1. Click the Data Cleaning button on XLTools ribbon Select Delete characters from the drop-down list A dialogue box will open.

    2. 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.
  2. 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
  3. 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

  1. Click the Data Cleaning button on XLTools ribbon Select Delete characters from the drop-down list A dialogue box will open.

    2. 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.
  2. 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
  3. 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

  1. Click the Data Cleaning button on XLTools ribbon Select Delete characters from the drop-down list A dialogue box will open.

    2. 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.
  2. Choose all characters except Choose the character type you want to remain:
    • numbers
    • letters
    • letters and numbers
    Select the range where to delete characters
  3. 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.

How to set options for deleting substrings from Excel cells
Any questions or suggestions?
How can we help?