![\"Visual](\"https:\/\/www.excel-learn.com\/wp-content\/uploads\/2023\/03\/VBA-Replace.jpg\")
<\/p>\n<\/p>\n
VBA Replace function<\/a> is used to replace a given substring (expression)<\/strong> in the source string<\/strong>.<\/p>\n<\/div>\n Syntax:<\/strong><\/p>\n In the examples below we will show you usage and explain the arguments in the Replace function.<\/p>\n Our expression (source string)<\/strong> in the example below is:<\/p>\n VBA code:<\/strong><\/p>\n Output:<\/strong><\/p>\n So, in the above example, we replaced the word \u201cis\u201d with \u201cwas\u201d.<\/p>\n You can specify where to start replacement in the source string by using the Start<\/strong> argument.<\/p>\n For example, our source string is:<\/p>\n \u201cThis is good\u201d<\/p>\n We specify start = 5<\/strong> in the example below:<\/p>\n Result:<\/strong><\/p>\n So returned result was only replaced from the 5th<\/sup> character and returned the remaining string after replacement.<\/p>\n Consider this string that we will use for our example below:<\/p>\n \u201cCOBOL is cool! VBA is good\u201d<\/p><\/blockquote>\n We only want to replace \u201cis\u201d with \u201cwas\u201d for the first sentence and want to get a complete sentence. See how it is done:<\/p>\n Output:<\/strong><\/p>\n If we had not used Count, the output after using this line:<\/p>\n <\/p>\n The default behavior of the Replace function is case-sensitive<\/strong> replacements. The example below shows this:<\/p>\n Result:<\/strong><\/p>\n So, we wanted to replace \u201cgood\u201d with \u201cAwesome\u201d and replace only done for starting with a small \u2018g\u2019.<\/p>\n So, if you want to perform case-insensitive<\/strong> replacements, the answer is simple:<\/p>\n Use the sixth argument - Compare<\/strong><\/p>\n The compare argument has the following possible constant values:<\/p>\nReplace(expression, find, replace, [ start, [ count, [ compare ]]])<\/div>\nAn example of using the expression, find and replace arguments<\/strong><\/h3>\n
\u201cThat is good!\u201d<\/div>\n\n\n
Sub replace_ex()\r\n\r\nDim Str As String\r\n\r\n\u00a0\u00a0\u00a0 Str = \"That is good!\"\r\n\r\n\u00a0\u00a0\u00a0 MsgBox (Replace(Str, \"is\", \"was\"))\r\n\r\nEnd Sub<\/pre>\n
![\"VBA-repalce-example\"](\"https:\/\/www.excel-learn.com\/wp-content\/uploads\/2023\/03\/74_1-VBA-repalce-example.png\")
<\/p>\nThe example of using the start argument in the Replace function<\/strong><\/h3>\n
Sub replace_ex()\r\n\r\nDim Str As String\r\n\r\n\u00a0\u00a0\u00a0 Str = \"This is good!\"\r\n\r\n\u00a0\u00a0\u00a0 MsgBox (Replace(Str, \"is\", \"was\", 5))\r\n\r\nEnd Sub<\/pre>\n
![\"VBA-repalce-start\"](\"https:\/\/www.excel-learn.com\/wp-content\/uploads\/2023\/03\/74_2-VBA-repalce-start.png\")
<\/p>\nUsing the count argument example<\/strong><\/h3>\n
If you want to restrict the number of replacements in the source string then use the Count<\/strong> argument.<\/div>\nSub replace_ex()\r\n\r\nDim Str As String\r\n\r\n\u00a0\u00a0\u00a0 Str = \"COBOL is cool! VBA is good\"\r\n\r\n\u00a0\u00a0\u00a0 MsgBox \"String After Replace = \" & (Replace(Str, \"is\", \"was\", , 1))\r\n\r\nEnd Sub<\/pre>\n
![\"VBA-repalce-count\"](\"https:\/\/www.excel-learn.com\/wp-content\/uploads\/2023\/03\/74_3-VBA-repalce-count.png\")
<\/p>\nMsgBox \"String After Replace = \" & (Replace(Str, \"is\", \"was\"))<\/div>\n![\"VBA-repalce-count-2\"](\"https:\/\/www.excel-learn.com\/wp-content\/uploads\/2023\/03\/74_4-VBA-repalce-count-2.png\")
<\/p>\nAlso, notice that we did not provide any value for the Start<\/strong> argument. So, the default value 1<\/strong> is taken which means start replacing from the beginning of the source string.<\/div>\nDoes Replace perform case-sensitive replacements?<\/strong><\/h3>\n
Sub replace_ex()\r\n\r\nDim Str As String\r\n\r\n\u00a0\u00a0\u00a0 Str = \"COBOL was Good! VBA is good\"\r\n\r\n\u00a0\u00a0\u00a0 MsgBox \"String After Replace = \" & (Replace(Str, \"good\", \"Awesome\"))\r\n\r\nEnd Sub<\/pre>\n
![\"VBA-repalce-case\"](\"https:\/\/www.excel-learn.com\/wp-content\/uploads\/2023\/03\/74_5-VBA-repalce-case.png\")
<\/p>\nHow to perform case-insensitive by Compare argument<\/strong><\/h3>\n
![\"VBA-repalce-case-insensitive\"](\"https:\/\/www.excel-learn.com\/wp-content\/uploads\/2023\/03\/74_6-VBA-repalce-case-insen.png\")
<\/p>\n![\"VBA-repalce-cell\"](\"https:\/\/www.excel-learn.com\/wp-content\/uploads\/2023\/03\/74_7-VBA-repalce-cell.png\")
<\/p>\n![\"VBA-repalce-rangel\"](\"https:\/\/www.excel-learn.com\/wp-content\/uploads\/2023\/03\/74_8-VBA-repalce-rangel.png\")
<\/p>\n","protected":false},"excerpt":{"rendered":"