Using the REPLACE and REPLACEB Functions in Formulas and the Find and Replace Tool in Excel 2007 and Excel 2010
Hi and welcome to my latest article on Excel. Today, I am going to look at the REPLACE function in detail and also compare it with the Find and Replace tool in Excel and investigate when to use one or the other to replace data.
The REPLACE and REPLACEB functionsallow you to change the contents of a cell or cells in a very precise way. For example, you could replace the fifth character in each cell with a specific character. You can also combine it with other functions such as the IF function to replace for example, the fifth character with a specific character (or characters) only if the text in a cell starts with an S.
The Find and Replace tool and the REPLACE / REPLACEB functions differ in a number of ways:
Find and Replace allows you to:
- Only change the entire contents of a cell
- It allows you to change the cells formatting in a wide number of different ways using the same options that are available via the Format Cells dialogue box (available when you right click on a cell)
- You can also match case and / or the entire cell’s contents with a single click
- The tool can also search within specific sheets or the whole workbook
The REPLACE function is much more powerful, but more specific in its function:
- You can change a specific part of the cells contents or the entire cell
- It can be combined with other functions such as IF, LEFT and RIGHT to change a cell’s contents only in very specific cases.
The REPLACEB function is identical to REPLACE with one important difference. REPLACEB is designed to work with double-byte characters such as Traditional or Simplified Chinese (each character in Chinese is counted as two in Excel so REPLACEB counts double-byte characters correctly to ensure that accurate results are returned).
Using Find and Replace in Excel 2007 and Excel 2010
The Find and Replace tool in Excel is a very useful tool for replacing the contents of an entire cell (or cells) with new contents. It is quite flexible in its usage, but is limited to only replacing the entire contents. It is often used to fix data entry errors in the data, such as data entry errors or to ensure your data is consistent and uniformly formatted.
To illustrate how to use Find and Replace, we will work through a short example.
I have a workbook and I would like to reformat some cells quickly and easily. I want to change every cell that contains Wednesday to Wed. and also ensure they are stored as text and that the font is consistent.To do this:
- Find and Replace is found on the Home tab, in the Editing group by clicking on the Find & Select button.
- Select the Replace tab
- In Find what, enter Wednesday (or what it is you are looking for)
- In Replace with, enter Wed. (or whatever you want to replace)
- To ensure that we only replace Wednesday with Wed. for cells that only contain Wednesday, click Match entire cell contents
- Next, click the Format button
- Select any formatting you want to change when you replace the cell’s contents (I clicked on the Number tab and selected Text, to ensure all cells that are replaced are formatted as text and then click on Font to standardise the font)
- You can either select Replace All to replace all at once, or Replace to replace them individually
Find and Replace will then tell you how many replacements were made:
Note: Wildcards can also be used in the Find what field, so to replace any cell that starts with Wed we could use Wed*. The ? can be used to represent a single character, so Wed? would allow you to replace any cell that contains a four letter word starting with Wed.
Using the REPLACE and REPLACEB Functions in a Formula in Excel 2007 and Excel 2010
The REPLACE function is made up of four parts:
- The text you are replacing
- The starting position in the cell of the text you are replacing
- The number of characters you are replacing
- The new text you are placing in the cell
To illustrate this in an example: I have a number of cells containing days of the week. I want to shorten them so Monday will become Mon., Tuesday will become Tue. etc.
The formula I will use to achieve this is:
The figure below shows the formula in action. I have copied the formula to the entire column to illustrate the results.
In English, what I have asked Excel to do is to go to fourth character in cell A2 and then starting with that character replace the next five characters with a “.”.
Finally, to update column A so that it looks like column E, I select the data in column E and use Copy and then using Paste Special I paste the Values into column A.
Using the REPLACE Function With the IF Function in Formulas in Excel 2007 and Excel 2010
The power of Excel lies in combining functions together to make more powerful and more useful formulas. To illustrate this, I am going to combine the REPLACE, IF and LEFT functions together in a formula.
The LEFT function allows you to focus on a specific number of characters on the left hand side of the cell and do something to them, such as copying them to another cell or splitting the text into multiple cells. The RIGHT function works the same as LEFT but works on the characters on the right hand side of a cell. MID allows you to specify a certain number of characters from anywhere in the cell. Finally, CONCATENATE allows you to join the contents of two cells together. I have an article that goes into the LEFT, RIGHT and CONCATENATE functions in much more detail. That article can be found here.
The IF function is a very powerful function and allows you to have Excel do one thing if the result is true and another if it is false. So you can ask Excel to replace part of the cell’s contents if “Wed” is found in the cell and do nothing if it is not found. You can combine it with logical functions to make the formulas even more powerful so for example, IF Billy’s exam result is more than 75 AND less than 90, Billy gets a B in his Geography exam. I have an article on using IF with the logical functions AND, OR and NOT as well as using IFERROR to suppress known or expected errors which can be found here.
To illustrate this, let’s use an example. If a cells first three letters are Wed then we want to shorten it to Wed. If the cell contains anything else we want it to display a space “ “. To begin with, the formula is made up of three functions, IF, LEFT and REPLACE.
In the above figure, I have colour coded the formula. The IF function is in blue, the LEFT function is in red and the REPLACE function is in green.
- The REPLACE functions syntax is the same as the above example
- The LEFT functions part simply says look at the 3 leftmost characters in cell A2
- The IF statement says that IF the three leftmost characters in A2 are “Wed”, then replace the six characters starting from character four with a “.” and if A2 doesn’t contain “Wed” then put in a space
The REPLACE and REPLACEB functions are ideally suited for changing part of a cell’s contents and can be used with other functions to create very specific and powerful formulas. In today’s examples, we looked at using REPLACE on its own to replace Wednesday with Wed. and Tuesday with Tue in a range of cells. We then looked at combining it with IF and LEFT to replace Wednesday with Wed. and enter a “ “ if a cell contained anything else. REPLACEB works identically to REPLACE except that REPLACEB is designed to work with dual-byte characters such as Traditional and Simplified Chinese.
We also looked at the Find and Replace tool and explored its functionality. Find and Replace is perfect for replacing the entire contents of a cell with something else, so ideal for making changes to a large number of cells quickly and easily. You can also change the formatting of a cell using Find and Replace, changing fonts or adding borders etc.
I hope that you have found this article interesting and informative and that you now know when to use Find and Replace and how and when to use REPLACE or REPLACEB. Many thanks for reading, please feel free to leave any comments you may have below.
More by this Author
- 2Use OFFSET and COUNTA to create dynamic ranges with automatically updating formulas in Excel 2007 and Excel 2010
The OFFSET and COUNTA functions allow you to create dynamic ranges of data. Formulas created to SUM this data (for example) will automatically update if data is added or removed from within the range.
Shapes in Excel 2007 and Excel 2010 can be renamed and reordered using the Selection Pane. To save time, shapes can also be grouped so that they can be formatted, manipulated or moved together.
If your data range contains many blank cells, it can be hard to correctly create a chart. Use the IF function to convert blank cells to #N/A which Excel ignores or change how Excel deals with blanks.