There could be various reasons why we need to change and substitute certain text in a cell. We have touched on this previously when needed to take out certain part of a string in a cell (How to Extract Part of a Text in Cells). You could be doing this to clean up data or simply to help with reporting. Whatever reason it is, in this article we will go through two ways on how to change and replace specific text in Excel.
Find and Replace
We will first go through the more simple way of doing this. And that is to use the Find and Replace function in Excel. The hotkey for it is CTRL & H. Alternatively we can find it in the Home tab, in the Editing section > Find & Select > Replace…:
Here we could find and replace any text we would like. If no cell is selected, the “Find and Replace” will apply to everything in the worksheet. However we can have selected cells highlighted and only replace specific texts for those certain cells.
For example below, we only have A2:A10 highlighted and we will replace all “PCS” to “MQCS”:
Click “Replace All”:
Simple to use but has certain limitations:
- In a cell where the text appear in multiple instances, we cannot specify whether we only want to replace the first or second or third text. E.g. in the example above, we could not use “Find and Replace” to only replace the second dash
- Not being in a formula or function also means we cannot wrap this with another formula. And to not change the original data, we would have to copy and paste data into another location, do a “Find and Replace” and then apply another formula/function to it. That is just not the most efficient way to use Excel.
With that in mind, we will now explore another method to change and replace specific texts in Excel. This time, with a built-in Excel function.
SUBSTITUTE function
There is a built-in function in Excel called SUBSTITUTE and its purpose is to:
- Replace existing text in a string with new text
This function requires 3 mandatory inputs and 1 optional variable:
- The text or cell reference: this can also be an array in which case the results will spill into adjacent cells
- Old text: the existing text in the string to be replaced
- New text: what the old existing text should be placed with
- [Instance_num]: this is the optional input. It is the variable which allows us to choose, in cases where the existing text appears in multiple occasions, which existing text to replace in the string. By default if this is blank, all of them will be replaced.
Using the above example again, with the SUBSTITUTE function, we can change and replace the second dash “-” with another character and leave the first one unchanged:
=SUBSTITUTE(A2, “-“, “@”, 2)
Here we are changing the text in cell A2. We are changing “-” to “@” and only the second instance “-” comes up. Note that if we put a number for [instance_num] that is more than the number of times the old existing text appears in the cell, Excel will just not change and replace any of them. And if we put 0 or a negative number, we will get #VALUE error.
Why Is This Useful?
There could be a number of reasons why this would be useful. One thing we can agree on is that it is definitely more flexible than “Find and Replace” or CTRL & H. With the example above, after replacing the second dash with “@”, we can now wrap the SUBSTITUTE function inside a FIND function and this will help us find where the second dash (now replaced with “@”) was:
- =FIND(“@”,SUBSTITUTE(A2,”-“,”@”,2))
And with that, we can now use the RIGHT function to extract the names of all the employees in the list:
- =RIGHT(A2,LEN(A2)-FIND(“@”,SUBSTITUTE(A2,”-“,”@”,2)))
We hope you find this article useful. If you have any more questions regarding changing and replacing certain texts in Excel, please leave a comment below and we will keep editing this article to make it more and more useful for everyone.