{"id":386,"date":"2017-01-30T09:59:42","date_gmt":"2017-01-30T04:29:42","guid":{"rendered":"http:\/\/exceltrick.com\/?p=386"},"modified":"2023-08-11T23:24:25","modified_gmt":"2023-08-11T17:54:25","slug":"vba-replace-function","status":"publish","type":"post","link":"https:\/\/exceltrick.com\/vba\/vba-replace-function\/","title":{"rendered":"VBA Replace Function &#8211; How to Use in Excel"},"content":{"rendered":"<p>VBA Replace is a quite useful <a title=\"VBA Substring \u2013 How to Substring in Excel Macro\" href=\"http:\/\/exceltrick.com\/vba\/vba-substring-function\/\">string function<\/a> in Excel VBA. Functions like replace ease your tasks while dealing with strings.<\/p>\n<p>As the name suggests the job of the Replace function is to substitute a set of characters in a string with a new set of characters.<\/p>\n<p>In Excel VBA there are two functions that can be used for performing string replace operations. And today I will highlight and give examples of both these functions:<\/p>\n<a href=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/VBA_Replace.jpg\"><img decoding=\"async\" class=\"aligncenter size-full wp-image-393\" src=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/VBA_Replace.jpg\" alt=\"VBA Replace Function\" width=\"397\" height=\"397\" title=\"\"><\/a>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_53 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\" role=\"button\"><label for=\"item-69f3baa9bf328\" ><span class=\"\"><span style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input aria-label=\"Toggle\" aria-label=\"item-69f3baa9bf328\"  type=\"checkbox\" id=\"item-69f3baa9bf328\"><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/exceltrick.com\/vba\/vba-replace-function\/#1_VBA_REPLACE_Function\" title=\"1. VBA REPLACE Function\">1. VBA REPLACE Function<\/a><ul class='ez-toc-list-level-3'><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/exceltrick.com\/vba\/vba-replace-function\/#Examples_of_VBA_REPLACE_Function\" title=\"Examples of VBA REPLACE Function\">Examples of VBA REPLACE Function<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/exceltrick.com\/vba\/vba-replace-function\/#2_Excel_VBA_SUBSTITUTE_Function\" title=\"2. Excel VBA SUBSTITUTE Function:\">2. Excel VBA SUBSTITUTE Function:<\/a><ul class='ez-toc-list-level-3'><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/exceltrick.com\/vba\/vba-replace-function\/#Examples_of_VBA_SUBSTITUTE_Function\" title=\"Examples of VBA SUBSTITUTE Function:\">Examples of VBA SUBSTITUTE Function:<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/exceltrick.com\/vba\/vba-replace-function\/#Excel_Macro_Using_Above_Functions\" title=\"Excel Macro Using Above Functions:\">Excel Macro Using Above Functions:<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"1_VBA_REPLACE_Function\"><\/span>1. VBA REPLACE Function<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>As I have foretold the Replace function simply replaces a set of characters from a string with another predefined set of characters.<\/p>\n<p>The basic syntax of a <i>VBA Replace function<\/i> is as follows:<\/p>\n<div class=\"vbaFormula\">\n<pre style=\"margin: 0; line-height: 125%;\"><span style=\"color: #666666;\">=<\/span>Replace( Source_string, Old_string, Replacement_string, [start, [count, [compare]]] )<\/pre>\n<\/div>\n<p style=\"padding-left: 30px;\">Here, \u2018Source_string\u2019 is the string from which we have to replace the characters.<\/p>\n<p style=\"padding-left: 30px;\">\u2018Old_string\u2019 is a set of characters or a string which is to be replaced.<\/p>\n<p style=\"padding-left: 30px;\">\u2018Replacement_string\u2019 is a set of characters with which the \u2018Old_string\u2019 is to be replaced.<\/p>\n<p style=\"padding-left: 30px;\">\u2018start\u2019 represents the numerical position in the \u2018Source_string\u2019 from which the search should begin. It is an optional parameter. If we omit this parameter then the search begins at position 1.<\/p>\n<p style=\"padding-left: 30px;\">\u2018count\u2019 is the number of occurrences of \u2018Old_string\u2019 to be replaced. It is an optional parameter. If its value is omitted then all the occurrences of \u2018Old_string\u2019 in the \u2018Source_string\u2019 will be replaced.<\/p>\n<p style=\"padding-left: 30px;\">\u2018compare\u2019 is also an optional parameter. It signifies the type of comparison algorithm to be used while Replace Function searches for the occurrences of \u2018Old_string\u2019 in the \u2018Source_string\u2019. In most cases, it is better to omit this value. But for advanced cases, the values of this parameter can be any one of these.<\/p>\n<style type=\"text\/css\">\n    table.tableizer-table {<br \/>   border: 1px solid #CCC; font-family: <?php echo $tableFont ?>;<br \/>    font-size: 14px;<br \/>}<br \/>.tableizer-table td {<br \/>    padding: 4px;<br \/> margin: 3px;<br \/>  border: 1px solid #ccc;<br \/>}<br \/>.tableizer-table th {<br \/> background-color: #E17921;<br \/>    color: #FFF;<br \/>  font-weight: bold;<br \/>}<br \/><\/style>\n<table class=\"tableizer-table\">\n<tbody>\n<tr class=\"tableizer-firstrow\">\n<th>Parameter Value<\/th>\n<th>Description<\/th>\n<\/tr>\n<tr>\n<td>\u00a0vbBinaryCompare<\/td>\n<td>\u00a0Use this in case of a Binary comparison.<\/td>\n<\/tr>\n<tr>\n<td>\u00a0vbTextCompare<\/td>\n<td>\u00a0Use this in case of a Text to Text comparison<\/td>\n<\/tr>\n<tr>\n<td>\u00a0vbDatabaseCompare<\/td>\n<td>\u00a0It uses the locale settings of a Database for comparison rather than straight text.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h3><span class=\"ez-toc-section\" id=\"Examples_of_VBA_REPLACE_Function\"><\/span>Examples of VBA REPLACE Function<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><strong>Example 1:<\/strong><\/p>\n<div class=\"vbaFormula\">\n<pre style=\"margin: 0; line-height: 125%;\">Replace(<span style=\"color: #ba2121;\">\"Excel Tips\"<\/span>, <span style=\"color: #ba2121;\">\"Tips\"<\/span>, <span style=\"color: #ba2121;\">\"Trick\"<\/span>)<\/pre>\n<\/div>\n<p>This statement would return \u201cExcel Trick\u201d.<\/p>\n<p><strong>Example 2:<\/strong><\/p>\n<div class=\"vbaFormula\">\n<pre style=\"margin: 0; line-height: 125%;\">Replace(<span style=\"color: #ba2121;\">\"Excel VBA\"<\/span>, <span style=\"color: #ba2121;\">\"Tips\"<\/span>, <span style=\"color: #ba2121;\">\"Trick\"<\/span>)<\/pre>\n<\/div>\n<p>This statement would return \u201cExcel VBA\u201d as here we have asked the Replace function to replace \u201cTips\u201d but as you can see \u201cTips\u201d text string is not present inside the Source string, hence the output will be Source string as it is.<\/p>\n<p><strong>Example 3:<\/strong><\/p>\n<div class=\"vbaFormula\">\n<pre style=\"margin: 0; line-height: 125%;\">Replace(<span style=\"color: #ba2121;\">\"alligator\"<\/span>, <span style=\"color: #ba2121;\">\"a\"<\/span>, <span style=\"color: #ba2121;\">\"z\"<\/span>, <span style=\"color: #666666;\">2<\/span> )<\/pre>\n<\/div>\n<p>This statement will result in \u201calligztor\u201d as here the Replace statement starts searching for the character \u2018a\u2019 after the second position in the source string and then replaces it with \u2018z\u2019 character.<\/p>\n<p><strong>Example 4:<\/strong><\/p>\n<div class=\"vbaFormula\">\n<pre style=\"margin: 0; line-height: 125%;\">Replace(<span style=\"color: #ba2121;\">\"alligator\"<\/span>, <span style=\"color: #ba2121;\">\"a\"<\/span>, <span style=\"color: #ba2121;\">\"z\"<\/span>, <span style=\"color: #666666;\">1<\/span>, <span style=\"color: #666666;\">1<\/span>)<\/pre>\n<\/div>\n<p>This statement would result in \u201czlligator\u201d as here we have told the VBA Replace Statement to replace only one occurrence of \u2018a\u2019 with \u2018z\u2019.<\/p>\n<p><strong>Example 5:<\/strong><\/p>\n<div class=\"vbaFormula\">\n<pre style=\"margin: 0; line-height: 125%;\">Replace(<span style=\"color: #ba2121;\">\"Alligator\"<\/span>, <span style=\"color: #ba2121;\">\"a\"<\/span>, <span style=\"color: #ba2121;\">\"z\"<\/span>, <span style=\"color: #666666;\">1<\/span>, , vbTextCompare)<\/pre>\n<\/div>\n<p>The use of vbTextCompare in this statement tells the Replace Statement to ignore the difference between Upper Case and Lower Case characters hence this statement results in: \u201czalligztor\u201d.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"2_Excel_VBA_SUBSTITUTE_Function\"><\/span>2. Excel VBA SUBSTITUTE Function:<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The Excel VBA <a href=\"https:\/\/exceltrick.com\/functions\/excel-substitute-function\/\"  rel=\"noopener noreferrer\">SUBSTITUTE function<\/a> is very similar to the Replace Statement. The syntax of both these functions is almost the same, the results they deliver are the same too.<\/p>\n<p>The point that is very important while using the Substitute function is that: this function is not readily available in the VBA functions but under the VBA WorksheetFunctions. See, the syntax of the Substitute function below:<\/p>\n<div class=\"vbaFormula\">\n<pre style=\"margin: 0; line-height: 125%;\">WorksheetFunction.Substitute(<span style=\"color: #ba2121;\">\"Source_string\"<\/span>, <span style=\"color: #ba2121;\">\"Old_string\"<\/span>, <span style=\"color: #ba2121;\">\"Replacement_string\"<\/span>, Instance_num)<\/pre>\n<\/div>\n<p style=\"padding-left: 30px;\">Here also the \u2018Source_string\u2019 is the string from which we have to replace the characters.<\/p>\n<p style=\"padding-left: 30px;\">\u2018Old_string\u2019 is a set of characters which are to be replaced.<\/p>\n<p style=\"padding-left: 30px;\">\u2018Replacement_string\u2019 is a set of characters with which the \u2018Old_string\u2019 is to be replaced.<\/p>\n<p style=\"padding-left: 30px;\">\u2018Instance_num\u2019 is an integer parameter that specifies which occurrence of \u2018Old_string\u2019 you want to replace with \u2018Replacement_string\u2019. This is an optional argument. If it is omitted then all the occurrences of \u2018Old_string\u2019 will be replaced.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Examples_of_VBA_SUBSTITUTE_Function\"><\/span>Examples of VBA SUBSTITUTE Function:<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><strong>Example 1:<\/strong><\/p>\n<div class=\"vbaFormula\">\n<pre style=\"margin: 0; line-height: 125%;\">Substitute (<span style=\"color: #ba2121;\">\"Excel Tips\"<\/span>, <span style=\"color: #ba2121;\">\"Tips\"<\/span>, <span style=\"color: #ba2121;\">\"Trick\"<\/span>)<\/pre>\n<\/div>\n<p>This statement would return \u201cExcel Trick\u201d.<\/p>\n<p><strong>Example 2:<\/strong><\/p>\n<div class=\"vbaFormula\">\n<pre style=\"margin: 0; line-height: 125%;\">Substitute (<span style=\"color: #ba2121;\">\"H:\\SomeFolder\\AnotherFolder\\SomeFile.txt\"<\/span>, <span style=\"color: #ba2121;\">\"\\\"<\/span>, <span style=\"color: #ba2121;\">\"*\"<\/span>, <span style=\"color: #666666;\">3<\/span>)<\/pre>\n<\/div>\n<p>This statement returns: \u201cH:\\SomeFolder\\AnotherFolder<em>SomeFile.txt\u201d. As here only the third instance of \u201c\\\u201d character is replaced by \u201c<\/em>\u201d.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Excel_Macro_Using_Above_Functions\"><\/span>Excel Macro Using Above Functions:<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>I have created an Excel Macro in which I have used both the above functions. The macro is quite simple and self-explanatory.<\/p>\n<a href=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/VBA_Replace_Macro.png\"><img decoding=\"async\" class=\"aligncenter size-full wp-image-392\" src=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/VBA_Replace_Macro.png\" alt=\"VBA Replace macro\" width=\"658\" height=\"287\" title=\"\"><\/a>\n<p>The macro is as under:<\/p>\n<div class=\"vbaFormula\">\n<pre style=\"margin: 0; line-height: 125%;\"><span style=\"color: #008000; font-weight: bold;\">Sub<\/span> <span style=\"color: #0000ff;\">ReplaceFunctions<\/span>()<br clear=\"none\" \/>var_replace <span style=\"color: #666666;\">=<\/span> Replace(<span style=\"color: #ba2121;\">\"Excel Tip\"<\/span>, <span style=\"color: #ba2121;\">\"Tip\"<\/span>, <span style=\"color: #ba2121;\">\"Trick\"<\/span>)<br clear=\"none\" \/>var_substitute <span style=\"color: #666666;\">=<\/span> WorksheetFunction.Substitute(<span style=\"color: #ba2121;\">\"Excel Trick\"<\/span>, <span style=\"color: #ba2121;\">\"c\"<\/span>, <span style=\"color: #ba2121;\">\"z\"<\/span>, <span style=\"color: #666666;\">2<\/span>)<br clear=\"none\" \/>MsgBox <span style=\"color: #ba2121;\">\"Replace_Result- \"<\/span> <span style=\"color: #666666;\">&amp;<\/span> var_replace <span style=\"color: #666666;\">&amp;<\/span> vbNewLine <span style=\"color: #666666;\">&amp;<\/span> <span style=\"color: #ba2121;\">\"Substitute_Result- \"<\/span> <span style=\"color: #666666;\">&amp;<\/span> var_substitute<br clear=\"none\" \/><span style=\"color: #008000; font-weight: bold;\">End<\/span> <span style=\"color: #008000; font-weight: bold;\">Sub<\/span><\/pre>\n<\/div>\n<p>So, these are some of the VBA string Replace functions that can be used in Excel Macros.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>VBA Replace is a quite useful string function in Excel VBA. Functions like replace ease your tasks while dealing with strings. As the name suggests the job of the Replace function is to substitute a set of characters in a string with a new set of characters. In Excel VBA there are two functions that [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14],"tags":[9],"_links":{"self":[{"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/posts\/386"}],"collection":[{"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/comments?post=386"}],"version-history":[{"count":0,"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/posts\/386\/revisions"}],"wp:attachment":[{"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/media?parent=386"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/categories?post=386"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/tags?post=386"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}