{"id":31820,"date":"2023-11-08T11:34:44","date_gmt":"2023-11-08T10:34:44","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=31820"},"modified":"2024-03-22T18:06:22","modified_gmt":"2024-03-22T17:06:22","slug":"excel-substitute-function","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/excel-substitute-function\/","title":{"rendered":"SUBSTITUTE Function – Replace Characters in Excel!"},"content":{"rendered":"

\"SUBSTITUTE<\/a>
\nOne of the most common tasks in Excel is replacing specific characters within a cell’s content. Imagine you\u2019re working on a dataset that includes many products with spelling errors or inconsistencies. The SUBSTITUTE Function in Excel can be your life-saver! In this article, we’ll explore how to Replace Characters in Excel<\/a> with the SUBSTITUTE Function, its\u2019 comparison with the REPLACE Function, and some helpful examples –<\/p>\n

Download the Excel Workbook below to follow along and understand how to Replace Characters in Excel with the SUBSTITUTE Function \u2013<\/h4>\n

download excel workbook<\/strong><\/span>ReplaceCharacterswithSubstitute.xlsx<\/span><\/a><\/p>\n

<\/h3>\n

Syntax and Arguments<\/span><\/strong><\/h3>\n

The SUBSTITUTE<\/strong> function has the following syntax:<\/p>\n

=SUBSTITUTE(text, old_text, new_text, [instance_num])<\/strong><\/p>\n

text<\/strong>: This is the text string you want to modify.
\nold_text<\/strong>: This is the text you want to replace.
\nnew_text<\/strong>: This is the new text that will replace the old text.<\/p>\n

instance_num<\/strong> (optional): It specifies which occurrence of old_text to replace. If omitted, all occurrences are replaced.<\/p>\n

Basic Usage of SUBSTITUTE<\/span><\/strong><\/h3>\n

Let\u2019s say you want to substitute the space character with \u201c-\u201c in \u201cHigh tech\u201d To do this, follow these steps:<\/p>\n

Click on the cell where you want to place the modified text.<\/p>\n

In the cell or formula<\/a> bar, enter: =SUBSTITUTE(A2,\u201d \u201c,\u201d-\u201c)<\/strong><\/p>\n

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

After pressing Enter<\/strong>, the cell will display:<\/p>\n

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

You will be presented with the new data. It contains the character \u201c-\u201c instead of the space.<\/p>\n

Note: The SUBSTITUTE function is case-sensitive. To make it case-insensitive, you might need to use other functions like LOWER or UPPER for consistent replacements.<\/em><\/p>\n

Replacing Multiple Instances<\/span><\/strong><\/h3>\n

By default, SUBSTITUTE replaces all occurrences of `old_text` within `text`. As displayed here:
\n\"SUBSTITUTE<\/a><\/p>\n

If you want to replace only a specific instance, you can use the `instance_num` argument.<\/p>\n

For instance, if you want to replace the first occurrence of the space ” ” with “-,” you would modify the formula like this:<\/p>\n

=SUBSTITUTE(A2, ” “, “-“, 1)<\/strong><\/p>\n

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

As you can see, the SUBSTITUTE function replaced the first space, and not the second one. This is because we indicated 1 for the \u201cinstance_num\u2019.<\/p>\n

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

The Difference Between SUBSTITUTE and REPLACE<\/span><\/strong><\/h3>\n

If you’re looking to replace a specific text or number at a known position within your data, then the REPLACE function is your ideal choice. On the other hand, if the text or number you wish to replace appears multiple times throughout your data, the SUBSTITUTE function is the more suitable option.<\/p>\n

Click here<\/a> to learn how to Clean Data with Excel’s REPLACE Formula!<\/p>\n

Let\u2019s look at these steps to illustrate the differences better:<\/p>\n

Suppose we want to replace the word \u201cright<\/strong>\u201d from the phrase \u201cAs right as rain\u201d, to \u201cwrong<\/strong>\u201d.<\/p>\n

This is how we would do it with the REPLACE function:
\n=REPLACE(A2,4,5,\u201dwrong\u201d)<\/strong><\/p>\n

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

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

If we drag the formula down<\/strong>, you will notice that it replaced the word \u201cRIGHT\u201d even though it was capitalized. That\u2019s because the REPLACE function is not case-sensitive<\/strong>.<\/p>\n

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

If we drag the formula down further, you will notice that it will not replace the second \u201cright\u201d because we only specified for it to replace the first \u201cright\u201d.<\/p>\n

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

To use the SUBSTITUTE formula, enter =SUBSTITUTE(A7,\u201dright\u201d,\u201dwrong\u201d)<\/strong><\/p>\n

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

If we drag the the formula down a row, you\u2019ll notice that the SUBSTITUTE formula replace both words.<\/p>\n

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

If we drag the formula down once more, you will notice that the SUBSTITUTE formula did not replace the capitalized word. Demonstrating that the formula is case-sensitive.<\/p>\n

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

Practical Examples<\/span><\/strong><\/h3>\n

Let’s explore a few practical examples of how SUBSTITUTE can be used:<\/p>\n

Example 1 – Reformatting Dates<\/strong>:
\nSuppose you have a column of dates in the format “MM\/DD\/YYYY,” and you want to change it to “YYYY-MM-DD.” You can use SUBSTITUTE as follows:<\/p>\n

=SUBSTITUTE(A2, “\/”, “-“)<\/strong><\/p>\n

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

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

Example 2 – Correcting Misspellings<\/strong>:
\nIf you have a column with product names and some of them contain misspellings like “aquired” instead of “acquired,” you can correct them with SUBSTITUTE:<\/p>\n

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

=SUBSTITUTE(A6, “aquired”, “acquired”)<\/strong><\/p>\n

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

Click here<\/a> to learn how to Add A Comma in Excel between Names with SUBSTITUTE Formula<\/p>\n

Conclusion<\/strong>
\nThere you have it! By mastering the usage of SUBSTITUTE and knowing the difference between it and the REPLACE function, you can efficiently transform and format your data according to your specific needs.<\/p>\n

Click here<\/a> to access Microsoft\u2019s tutorial on How to Use the SUBSTITUTE Function!<\/p>\n

Click here<\/a> to learn the 9 Tips for Formatting Excel Spreadsheets Like a Pro!<\/p>\n","protected":false},"excerpt":{"rendered":"

One of the most common tasks in Excel is replacing specific characters within a cell’s content. Imagine you\u2019re working on a dataset that includes many products with spelling errors or inconsistencies. The SUBSTITUTE Function in Excel can be your life-saver! In this article, we’ll explore how to Replace Characters in Excel with the SUBSTITUTE Function, […]<\/p>\n","protected":false},"author":1,"featured_media":31822,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"SUBSTITUTE Function - Replace Characters in Excel!","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[3,10,304,306],"tags":[43,188,1441,1442,174],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/31820"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=31820"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/31820\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/31822"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=31820"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=31820"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=31820"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}