{"id":4336,"date":"2016-10-12T23:30:07","date_gmt":"2016-10-12T21:30:07","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=4336"},"modified":"2023-11-30T21:18:40","modified_gmt":"2023-11-30T20:18:40","slug":"cleaning-data-excels-substitute-formula","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/cleaning-data-excels-substitute-formula\/","title":{"rendered":"Cleaning Data with Excel’s SUBSTITUTE Formula"},"content":{"rendered":"

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

Substitutes new_text for old_text in a text string.<\/p>\n

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

=SUBSTITUTE(text<\/span>, old_text<\/span>, new_text<\/span>, [instance num]<\/span>)<\/p>\n

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

=SUBSTITUTE(This cell<\/span>, By this text character<\/span>, To this text character<\/span>, [In the 1st, 2nd\u2026instance it occurs]<\/span>)<\/p>\n

\n

When you needed to replace a specific text\u00a0in each word, and there is a pattern, Excel has just the formula for you.<\/p>\n

The SUBSTITUTE\u00a0<\/b>formula in Excel can help you replace one specific text\u00a0with another easily.<\/p>\n

In our example below, we have a list of\u00a0part numbers.<\/p>\n

We want to replace the second dash \u00a0–<\/strong> \u00a0with the number sign \u00a0#<\/strong> . This formula is able to do this for us.<\/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