With Ultimate Suite for Excel

Replace substrings in Excel

Easily change various fragments of your text values to any other ones you need by using the Replace Substrings feature in Substring Tools. The add-in finds the substrings you specify in your original column and creates a new one containing the modified values.

Before you start

Pay attention to the following aspects:

  • Only one column is processed at a time.
  • Locate the substrings to be looked for and the substrings that are to replace them 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 Replace Substrings tool.
  • The result is put in a neighboring column and your source data doesn't undergo any changes.

How to replace substrings

Click the Substring Tools icon on the Ablebits Data tab and start the tool by selecting Replace Substrings:
Start the Replace Substrings tool.
Here is the tool pane to work with:
Adjust the Replace 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 replaced 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. If the search for substrings should be case-sensitive, pick the Case-sensitive option.
  6. Tick Replace whole words only if you want partial matches to be ignored.

See an example:
This is an example.
The tool processes your data right after you click the Replace button. The resulting column appears on the right of the source one and the tool informs you about the work done:
The substrings are replaced.

Responses

Can wildcards be used, or can a string of line feeds and carriage returns interspersed with spaces and tabs be included?

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.