Table of Contents
Using an Excel formula, you can easily replace text in a cell if it contains a specific keyword. This can be done by using the “IF” and “SUBSTITUTE” functions together. The “IF” function will check if the cell contains the keyword, and if it does, the “SUBSTITUTE” function will replace the text with the desired replacement. This method can save time and effort by automatically replacing text in a cell, rather than manually editing each cell individually. It is a useful tool for organizing and manipulating data in an Excel spreadsheet.
Excel Formula: Replace If Cell Contains Specific Text
You can use the Find and Replace feature in Excel to replace the text in cells only if they contain specific text.
The following example shows how to use this feature in practice.
Example: Replace If Cell Contains Specific Text in Excel
Suppose we have the following dataset that shows the name and position of various basketball players on some team:
Suppose we would like to replace all of the text for each cell in the Position column that contains “Starting” anywhere in the cell.
To do so, highlight the cell range B2:B13.
Then type Ctrl + H to bring up the Find and Replace window.
Then type *Starting* in the Find what box and then type Starter in the Replace with box:
Next, click the Replace All button in the bottom left corner of the window.
The text of each cell that contains Starting anywhere in the cell will now be replaced with Starter.
A message box also appears that tells us a total of 3 replacements were made.
Note that we use asterisks in the Find what box (*Starting*) to specify that we wanted to replace all of the text in the cell if it contained “Starting” anywhere in the string.
If you instead only want to replace the text “Starting” with “Starter” then leave out the asterisks in the Find what box when using the Find and Replace feature.