Manage multiple substrings in Google Sheets

Run Substring Batch Toolkit

1. In the Google Sheets menu, go to Extensions > Power Tools > Start:
   
2. Find the Text group:
   
3. And then open Substring Batch Toolkit:
   

You'll see 4 different ways to process your data. All deal with multiple substrings in one go: remove, extract, replace and add. Pick the required group to proceed.

Remove multiple substrings

This first tool will search the selected range for the substrings from your list of values. If found, they will be removed from the range completely.

1. Enter the range where you'd like to remove multiple substrings.

   To select the range right from the sheet, click the Select range icon on the right of the field and either highlight cells of interest or hit Auto select:
   
   The latter will automatically pick up the used range (till the first blank row and column from your position on the sheet).

2. Enter the range with substrings to remove. It's basically a list of all those values you'd like to find and remove in the selected range.

   The Select range icon is also here for you to use. If you do, highlight the range from the sheet or click Auto select for the add-on to detect the used range (till the first blank row and column from your position on the sheet).
   

3. If it's important to remove the substrings of a certain text case only, tick off the Match case box.
4. Decide on the exact way to get rid of the found values:
   • Remove substrings will simply delete these exact words from cells leaving other contents intact.
   • If cells contain at least one substring you wish to remove, Clear cells will erase all cell contents whatsoever leaving blanks behind.
   • Delete entire rows will completely delete the rows containing at least one substring to remove.

Once you're ready, hit Run.

Extract multiple substrings

The next add-on will look for the words from your list in the selected range and cut them out. Each cut word will be placed in its own new column to the right.

1. Enter the range you'd like to modify.
2. Enter the range with substrings you'd like to extract.
3. Click the Select range icon to select the ranges directly from the sheets or make the tool pick them up automatically using the Auto select button:
   
4. If the substrings may occur in different text cases, you may want to use the Match case option.
5. By default, the add-on retrieves values and puts them into the column(s) to the right of the selected range: each word to a separate column. If there's existing data in those columns, it will be replaced by the extracted substrings.

   To prevent overwriting, check this box, and new columns will be inserted for the extracted substrings.

6. The last checkbox controls whether the add-on only copies or completely cuts the found substrings from the selected range.

When you're happy with the settings, press Run.

Replace multiple substrings

Here you will find multiple substrings & replace them with other special words — all in one go.

1. Enter the range where you need to find & replace substrings.
2. Enter the range containing the exact substrings you'd like to find & replace.
   Note. This range must contain 2 neighboring columns:

   • Values to find — in the 1st (leftmost) column.
   • Their corresponding replacements — in the 2nd (rightmost) column.

   

3. Make use of the Select range icon in both fields to select or pick up the ranges automatically:
   
4. If the text case matters, select Match case to consider it.

Click Run to find & replace all substrings.

Add multiple substrings

Adding substrings after/before certain words is yet another operation you can do in a batch:

1. Select the data range where you intend to add substrings.
2. Set up a 2-column range containing the following:
   • Substrings to find in the 1st column. Your values will be added right before or after them.
   • Related values to add in the 2nd column.

   

3. The Select range icon in each field lets you select the ranges right from your file or make the tool automatically detect them for you:
   
4. Tick off Match case to consider the text case of your substrings.
5. Choose either Before or After from the drop-down menu to add substrings to the found values accordingly.

Hit Run to add the corresponding records before or after the found substrings.