What does it do?

Replaces part of a text string, based on the number of characters you specify, with a different text string

Formula breakdown:

=REPLACE(old_text, start_num, num_chars, new_text)

What it means:

=REPLACE(this cell, starting from this number, all the way to this number, with this new text)

 


There are times when we are given a bunch of data that is not clean, is incorrect or just needs to be updated and we need to make some manual adjustments to it.

For example, we have a set of phone numbers that have incorrect area codes in it:

replace-00

We want to change the area code of 817, into the area code 123.

The REPLACE formula in Excel can help you change the text very very easily…goodbye to manual adjustments!

I explain how you can do this and please go to the bottom of the page to see the animated gif tutorial:

DOWNLOAD EXCEL WORKBOOK

 

STEP 1: We need to enter the Replace function next to the cell that we want to clean the data from:

=REPLACE

 

STEP 2: The Replace arguments:

old_text

Which text do we want to change?

Reference the cell that contains the text string:

=REPLACE(C10,

replace-01

 

start_num

Which character do we want to start the replacement from?

We want to replace the area code 817, which starts from the 4th character in the phone number sequence:

1st character is the +

2nd character is the 1

3rd character is the

4th character is the 8

=REPLACE(C10, 4,

 replace-02

 

num_chars

How many characters do we want to replace?

We want to replace the area code 817, which is 3 characters long:

=REPLACE(C10, 4, 3,

replace-03

 

new_text

What text will serve as the replacement?

We want to replace the area code 817 with the area code 123:

=REPLACE(C10, 4, 3, “123”)

replace-04

 

STEP 3: Do the same for the rest of the cells by dragging the REPLACE formula all the way down using the left mouse button.

Note that all of the phone numbers are now clean:

replace-05

replace

 

HELPFUL RESOURCE:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Extract First Name From Full Name in Excel There were countless times when I had a list of full names, and all I needed was the First Name. It would be time-consuming to manually get the first names one by one. Thank goodness there are formulas to make my life easier! In Excel, it's very easy to do that with the LEFT a...
Workday Formula in Excel What does it do? Adds/Subtracts a specified number of workdays to a Date, which will give you a Future/Past Date Formula breakdown: =WORKDAY(start_date, days, ) What it means: =WORKDAY(specified date, number of work days to add/subtract, ) If you want to cal...
Cleaning Data with Excel’s CLEAN Formula   What does it do? Removes all nonprintable characters from text Formula breakdown: =CLEAN(text) What it means: =CLEAN(this dirty text cell) There are times when imported text from other applications contain characters that are unprintable. The CLEAN...
Calculate Elapsed Time in Excel   What does it do? Converts a formula to text and lets you specify the display formatting by using special format strings Formula breakdown: =TEXT(value1 - value2, format text) What it means: =TEXT(formula, a text string enclosed in quotation marks) ...