Add substrings in Excel

With Ultimate Suite for Excel

If your text records are missing some values, you can insert them in no time with the Add Substrings feature from Substring Tools. Let the add-in know what and where to add and enjoy the work done.

Before you start

Here are the things to keep in mind:

  • Only one column is processed at a time.
  • Locate the substrings to be looked for and the substrings to be added in individual cells within two adjacent columns. They should be opposite each other like in the screenshot below:
    See an example of a substrings range for the Add Substrings tool.
  • The result is put in a neighboring column and your source data do not undergo any changes.

How to add substrings

To start working with the Add Substrings tool, click the Substring Tools icon on the Ablebits Data tab and opt for Add Substrings:
Start the Add Substrings tool.
The tool window will show up:
Adjust the Add Substrings settings.

  1. Select a column containing the data to be processed—the Source column.
  2. Using the icons on the right side of the Source column box, you can select your current table column or any other column you need.
  3. Select a range containing the substrings to be looked for and the substrings to be inserted—the Substrings range.
  4. Using the icons on the right side of the Substrings range box, you can expand your current selection to two neighboring columns or select another range.
  5. The substrings of interest can be added before the indicated substrings, after the indicated strings, at the beginning of a string or at the end of it.
    Note. If you choose to add substrings at the beginning or at the end of a string, they are inserted as many times as the indicated substrings are found in a cell.
  6. If the search for substrings should be case-sensitive, pick the Case-sensitive option.

Here is the example:
See an example.
To process your data, click the Add button. After the work is done, the tool will let you know how many cells it has modified:
The substrings are added.

To learn about the other Substring Tools features, visit the following help pages: