{"id":4790,"date":"2016-11-02T23:46:41","date_gmt":"2016-11-02T22:46:41","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=4790"},"modified":"2023-11-30T21:12:16","modified_gmt":"2023-11-30T20:12:16","slug":"cleaning-data-with-excels-replace-formula","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/cleaning-data-with-excels-replace-formula\/","title":{"rendered":"Cleaning Data with Excel’s REPLACE Formula"},"content":{"rendered":"

What does it do?<\/strong><\/em><\/p>\n

Replaces part of a text string, based on the number of characters you specify, with a different text string<\/p>\n

Formula breakdown:<\/strong><\/em><\/p>\n

=REPLACE(old_text, start_num<\/span>, num_chars<\/span>, new_text<\/span><\/span><\/span><\/span>)<\/p>\n

What it means:<\/strong><\/em><\/p>\n

=REPLACE(this cell<\/span>, starting from this number<\/span>, all the way to this number<\/span>, with this new text<\/span><\/span><\/span><\/span>)<\/p>\n

 <\/p>\n

\n

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.<\/p>\n

For example, we have a set of phone numbers that have incorrect area codes in it:<\/p>\n

\"Cleaning<\/a><\/p>\n

We want to change the area code of 817<\/em>, into the area code 123<\/em>.<\/p>\n

The REPLACE\u00a0<\/b>formula in Excel can help you change\u00a0the text very very easily…goodbye to manual adjustments!<\/p>\n

I explain how you can do this and please\u00a0go to the bottom of the page to see the animated gif tutorial:<\/p>\n

\n