Using the REPLACE and REPLACEB Functions in Formulas and the Find and Replace Tool in Excel 2007 and Excel 2010

Updated on October 14, 2016

Introduction

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

Using the Find and Replace tool in Excel 2007 and Excel 2010 to replace specific cell's contents with updated text.
Using the Find and Replace tool in Excel 2007 and Excel 2010 to replace specific cell's contents with updated text. | Source
  • 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:

The Find and Replace tool will advise you of how many replacements it has made in Excel 2007 and Excel 2010.
The Find and Replace tool will advise you of how many replacements it has made in Excel 2007 and Excel 2010. | Source

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:

=REPLACE(A2,4,5,”.”)

The figure below shows the formula in action. I have copied the formula to the entire column to illustrate the results.

Example of a formula utilising the REPLACE function in Excel 2007 and Excel 2010.
Example of a formula utilising the REPLACE function in Excel 2007 and Excel 2010. | Source

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.

=IF(LEFT(A2,3)="Wed",REPLACE(A2,4,6,".")," ")

Example of a formula using the IF, LEFT and REPLACE functions in Excel 2007 and Excel 2010.
Example of a formula using the IF, LEFT and REPLACE functions in Excel 2007 and Excel 2010. | Source

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

Illustration of a formula using IF, LEFT and REPLACE in Excel 2007 and Excel 2010.
Illustration of a formula using IF, LEFT and REPLACE in Excel 2007 and Excel 2010. | Source

Conclusion

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.

Questions & Answers

    Comments

      0 of 8192 characters used
      Post Comment

      • profile imageAUTHOR

        Robbie C Wilson 

        5 years ago

        Hi SidKemp,

        Glad that you found this hub useful, that is great to hear and thanks for your message. You definitely can use REPLACE on numerical values, stored both as text and as numbers. You can also use other cell addresses in the REPLACE function, for example =REPLACE(A5,4,6,A6). Make sure not to use " " around the cell reference so that Excel knows it is a reference and not text.

      • SidKemp profile image

        Sid Kemp 

        5 years ago from Boca Raton, Florida (near Miami and Palm Beach)

        Thanks, this is a very useful and well-written hub. I can see uses I will have for the Replace() function. Some questions: Will Replace work on numerical values, or only text strings? Also, is it possible to have what is inserted by the replace function be a variable, such as a relative cell address?

        Voted up, useful, and interesting.

      working

      This website uses cookies

      As a user in the EEA, your approval is needed on a few things. To provide a better website experience, turbofuture.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

      For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://turbofuture.com/privacy-policy#gdpr

      Show Details
      Necessary
      HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
      LoginThis is necessary to sign in to the HubPages Service.
      Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
      AkismetThis is used to detect comment spam. (Privacy Policy)
      HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
      HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
      Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
      CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
      Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
      Features
      Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
      Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
      Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
      Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
      Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
      VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
      PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
      Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
      MavenThis supports the Maven widget and search functionality. (Privacy Policy)
      Marketing
      Google AdSenseThis is an ad network. (Privacy Policy)
      Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
      Index ExchangeThis is an ad network. (Privacy Policy)
      SovrnThis is an ad network. (Privacy Policy)
      Facebook AdsThis is an ad network. (Privacy Policy)
      Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
      AppNexusThis is an ad network. (Privacy Policy)
      OpenxThis is an ad network. (Privacy Policy)
      Rubicon ProjectThis is an ad network. (Privacy Policy)
      TripleLiftThis is an ad network. (Privacy Policy)
      Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
      Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
      Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
      Statistics
      Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
      ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
      Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)