How to Use Excel Replace to Manipulate and Replace Your Data

Excel comes with a number of built in cell functions that allow you to quickly and easily manipulate the data within each cell. There are literally hundreds of functions available in excel. To understand how the functions work each function comes with a syntax statement that indicates what the parameters are for the function.

The following tutorial will show you various ways to use the replace function within your excel spreadsheets. The tutorial assumes that you have a good basic knowledge of excel. It assumes that you can enter values, create worksheets and know what formulas are and how to use them in your worksheets. To be able to apply the principles taught in this tutorial, it is a good idea to have completed a course in excel like excel for beginners.

To show you how to use the replace function, we have created a fictitious worksheet that we will use in our examples. The worksheet contains the names, numbers and other contact details of a company. To follow the tutorial, you can create a worksheet similar to the one below:

The syntax of the replace function

The replace function allows you to replace certain values within a cell with values you specify.

The syntax of the replace function is expressed as:

=REPLACE ( old_text; start_num; num_characters; new_text)

The replace function therefore allows you to specify four parameters to use to help you replace values within a cell.

The old_text parameter in the function tells excel which values it must replace or which cell contains the data you want to replace. The start_num tells excel where to start replacing characters within the cell. The num_characters tells excel how many characters you want to replace and the new_text specifies what text you want to use to replace the old text.

As our first example of using replace, we will assume that some of area codes have changed and we need to update our contact list. To automatically update our contact list, we can create a column for the new area codes and then use that column to replace the old contact numbers. Take a look at the updated example below:

To create a new column with updated contact details, you can use the REPLACE function specifying C3 as the old_text, starting at position 1 for the start_num since the characters we want to replace are at the beginning of the string, then specifying 3 as the number of characters to replace and finally specifying column E3 as the new values. The REPLACE function in excel would be expressed as follows:

The resulting worksheet would look like this:

Using REPLACE to add formatting.

You can also use the REPLACE function to add formatting to your worksheet. At the moment our contact numbers don’t look like phone numbers at all. The replace function can add hyphens to our contact numbers to make them look more like phone numbers. Instead of specifying characters to replace, we specify zero characters to replace which has the effect of adding characters to our numbers. Take a look at the following REPLACE function:

This function replaces the values in column F3 of our worksheet (the updated phone numbers) from the fourth character in the cell. It replaces zero characters, in other words it adds a character, and the character it adds is a hyphen. The result of the above replace function is the following:

We have added a first hyphen to our contact number, but generally contact numbers contain two hyphens. You can achieve the new formatting by adding another column to the worksheet and using the REPLACE function again, but you can also nest one REPLACE function within the original function to achieve this result. Nesting means placing one function within another function. To learn more about nesting and the use of functions and formulas you can learn to use excel like a pro at udemy.

To nest the REPLACE function and add two hyphens, take a look at the structure of the following replace function:

The above function adds two hyphens to the numbers using the nested version of the REPLACE function. The result of the above function in our worksheet looks like this:

Using the replace function can save you a lot of time where it comes to updated details and values within your spreadsheet.

So far our examples have been to replace values with values of a similar nature. We have replace 3 characters from each cell with three new characters and our characters have all been in the same position in each cell. But life is often more complicated than that. There are often situations where you may need to replace a variable number of characters within a worksheet or cases where the characters do not necessarily appear in the same place in each cell.

Take a look at the worksheet below:

Let us assume that ABC company has changed its name to DEF and you need to update the email addresses of each person who is from ABC company. Their name lengths vary so using the ordinary REPLACE function will not work because we can’t specify where the company name begins. In this case we can use the FIND function to create the formula to allow us to replace the details within the email address.

Take a look at the function below:

The IF and FIND functions are not part of the scope of this tutorial. For more training on the use of excel functions you could take an advanced excel course. This formula tells excel to search each cell and if “abc” is found then it must replace the contents of cell, and instead of specifying a start character, the function uses the FIND function to locate where the company name begins, and once it has found it, excel must replace it with the new characters def.

The resulting worksheet will look like this:

As you can see the replace function can be a very powerful way to update details within your spreadsheet. Excel is a powerful way to keep and store data. For more advanced lessons and tutorials that will unleash your excel expertise, try an excel course from udemy like mastering excel which will have mastering excel within a weekend.