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

Add text strings at the beginning, at the end, or at another position in Excel cells

How to add substrings into Excel cells with XLTools add-in

When you need to insert some text into each Excel cell, creating functions or using VBA can be quite time consuming. It gets even more complicated if you need, for example, to add text into a specific position in a cell or before specific text.

The Add Characters feature helps add custom substrings (letters, numbers, spaces, commas or any other characters) to cells in seconds:

  • Add at the beginning or at the end of each cell
  • Add in the middle of all cells
  • Add before or after specific text

Before you begin, add Add Characters to Excel

Add 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
– free trial gives you 14 days of full access to all features.

How to add characters at the beginning of cells

  1. Click the Data Cleaning button on XLTools ribbon Select Add characters from the drop-down list A dialogue box will open.
  2. Select the range where you want to add characters.

    Tip: click on any cell in a table, and the entire table will be automatically selected.
  3. Type the substring you want to add to each cell.
  4. Choose the position in a cell: at the beginning.

    Select the range to where to add the substring
  5. Click OK Done, the text string is added at the beginning of each cell.

    See result: the substring is added at the beginning of each cell

How to add characters at the end of cells

  1. Click the Data Cleaning button on XLTools ribbon Select Add characters from the drop-down list A dialogue box will open.
  2. Select the range where you want to add characters.

    Tip: click on any cell in a table, and the entire table will be automatically selected.
  3. Type the substring you want to add to each cell.
  4. Choose the position in a cell: at the end.

    Select the range to where to add the substring
  5. Click OK Done, the text string is added at the end of each cell.

    See result: the substring is added at the end of each cell

How to add characters in the middle of cells

  1. Click the Data Cleaning button on XLTools ribbon Select Add characters from the drop-down list A dialogue box will open.
  2. Select the range where you want to add characters.

    Tip: click on any cell in a table, and the entire table will be automatically selected.
  3. Type the substring you want to add to each cell.
  4. Choose the position in a cell: after character number N Specify the number of characters from the beginning, where you want to insert the text string.

    Select the range to where to add the substring
  5. Click OK Done, the text string is added in the middle of each cell.

    See result: the substring is added in the middle of each cell

How to add characters before or after specific text in cells

  1. Click the Data Cleaning button on XLTools ribbon Select Add characters from the drop-down list A dialogue box will open.
  2. Select the range where you want to add characters.

    Tip: click on any cell in a table, and the entire table will be automatically selected.
  3. Type the substring you want to add to each cell.
  4. Choose the position in a cell: before this text or after this text Type the text string to search for (case sensitive by default).

    Select the range to where to add the substring
  5. Click OK Done, the text string is added before or after the specified text in those cells, where it was found.

    See result: the substring is added before or after specific text in cells

Set options for adding characters to cells

By default, the Add Characters feature does not process cells that contain formulas, not to interfere with calculations. You can specify additional data processing options:

  • Check the Skip empty cells option

    Select this option, if you don’t want to add the characters into empty (blank) cells.

  • 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 adding substrings into Excel cells
Any questions or suggestions?
How can we help?