How to replace name in Excel

How To Use The REPLACE Function In Excel: Replace Text EasilyWritten by co-founder Kasper Langmann, Microsoft Office Specialist.If you need to make edits to a large body of text, E

How to replace name in Excel

How To Use The REPLACE Function In Excel: Replace Text Easily

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

If you need to make edits to a large body of text, Excels functions can make the process much easier. Both the REPLACE and SUBSTITUTE functions can helpbut they both do it differently.

Lets take a look at how the two functions work, how they differ, and how you might put them to use in a real spreadsheet.

Table of Contents


2: Grab your free exercise file here!

3: Replacing characters with REPLACE

4: Replacing strings with SUBSTITUTE

5: Surgically replace with ease


Before we get into the details, we should look at the subtle difference between the REPLACE and SUBSTITUTE functions.

Both of them replace a string (or partial string) of text with another string.

The difference lies in how the first string is identified.

REPLACE selects the first string based on the position. So you might replace four characters, starting with the sixth character in the string.

SUBSTITUTE selects based on whether the string matches a predefined search. You might tell Excel to replace any instance of 2016 with 2017, for example.

Other than that,the two functions are identical.

Ill be using replace to talk about both of these actions. If you see replace written in lower-case letters, Im referring to the action, not the function.

Kasper Langmann, Co-founder of Spreadsheeto

Get your FREE exercise file

Weve put together an example workbook to help you learn the REPLACE and SUBSTITUTE functions.

Download it below and follow along with the article. Itll be much easier to learn!

Download the FREE Exercise File

Download free exercise file

Replacing characters with REPLACE

Well start with REPLACE. Lets take a look at the syntax first:

The syntax of the REPLACE function

=REPLACE(old_text, start_num, num_chars, new_text)

old_text is the cell containing the text you want to replace.

start_num tells Excels where the text you want to replace starts within the cell.

num_chars is the number of characters you want to replace.

new_text is the text youd like to replace that number of characters.

Keep in mind thatnew_textdoes not need to be the same length as num_chars. You could tell Excel to replace K with Kasper, and it would work.

Open up the example workbook, and take a look at the first sheet. Youll see that we have a sequence of ID numbers, with associated birth dates, and names in another cell.

Well use REPLACE to remove the ID numbers and replace them with the names. At the end, the names and the dates will be in the same cells, and well have gotten rid of the (presumably confidential) ID numbers.

To get started, click into cell C2.

Then type the following formula:

=REPLACE(A2, 1, 10, B2)

Looking back to the syntax definition, you can see that A2 isold_text, and B2 isnew_text. So Excel will take text from B2 and replace some string from A2 with it. Were using 1 asstart_num, to make sure Excel starts at the beginning of the cell. And because the ID numbers are 10 digits long, we used 10 as thenum_chars.

If you were to read this formula, youd say something like this: Replace ten characters, starting with the first, from cell A2, with the contents of cell B2.

HitEnter and lets see what happens.

Excel took the first ten digits of A2 and replaced them with the contents of B2! Exactly what we wanted.

Now use the fill handle to drag B2 down to the end of the column:

No matter how many characters are in each records name, Excel replaces only ten characters.

Kasper Langmann, Co-founder of Spreadsheeto

REPLACE works if youre always replacing the same number of characters, and theyre always in the same place in the cell.

Lets take a look at SUBSTITUTE, which may be more useful if your data isnt quite as clean as it needs to be for REPLACE.

Replacing strings with SUBSTITUTE

If the string you want to replace doesnt always appear in the same place, youll be better off with SUBSTITUTE. It has its own limitations, like REPLACE, but it can also be very useful. Lets take a look at the syntax:

The syntax of the SUBSTITUTE function

=SUBSTITUTE(text, old_text, new_text, [instance_num])

This is a little different from the last syntax we looked at, so be careful not to get them mixed up.

text is the cell that contains the string you want replaced.

old_text is the sequence of characters that you want Excel to replace.

new_text is what Excel will insert in its place.

instance_num is optional, and tells Excel how many times it should replaceold_text if it finds more than one instance.

Well take a look at that last one in a moment to clear it up.

On the second sheet of the example workbook, youll see a list of dates. If the dates were all formatted in the same way, we could use REPLACE to change the years. But because they have very different formats, well use SUBSTITUTE instead.

Click into cell B2. Heres the formula well use:

=SUBSTITUTE(A2, 2016, 2017)

You can probably see what this will do: Excel will search cell A2 for the string 2016, and if its there, replace it with 2017.

HitEnter and lets see how it works!


Excel replaced 2016 with 2017.

To see where SUBSTITUTE really shines, though, well need to use the fill handle to drag the formula down through the entire column.

Kasper Langmann, Co-founder of Spreadsheeto

As you can see, Excel replaced 2016 with 2017 no matter where it found the first string.

Thats where SUBSTITUTE differs from REPLACE.

Making multiple replacements with SUBSTITUTE

Remember theinstance_num from the syntax above? Dont forget about itits a useful tool.

When you leave the argument blank, Excel replaces every instance ofold_text that it can find. So if we used the above formula on a cell that contained 2016 2016 2016, the result would have been 2017 2017 2017.

With aninstance_num of one, we would have gotten 2017 2016 2016. With aninstance_num of 2, it would have been 2017 2017 2016.

This can be very useful when you have repetitive data and you only want to replace some of the matching strings.

Surgically replace with ease

With REPLACE and SUBSTITUTE, you can replace very specific strings with other strings. You can use letters, numbers, or other characters.

In short, you can replace text with extreme accuracy. And that saves you a great deal of time when you need to make a lot of edits.

If you combine REPLACE and SUBSTITUTE with other functions, you can do even more impressive replacements.

Try to come up with a combination of functions that will save you time in your work!

Kasper Langmann, Co-founder of Spreadsheeto

Video liên quan