Return to VBA Code Examples

VBA Replace Function – Replace String

This tutorial will demonstrate how to use the Replace VBA function to replace strings of text.

Replace Function

The VBA Replace function returns a string, with substitutions made. With the use of optional parameters, the new string can begin at the position specified by start and ends at the end of the original string.

VBA Replace Substitute a Substring

The VBA Replace function can be used to substitute any substring found, in every part it is found.

VBA Replace Starting Position

The VBA Replace function can be used to substitute any substring found, in every part it is found. If we assign a start position then the result would be the part of the original string after that starting point.

VBA Replace a Few Occurrences Only

The VBA Replace function can be used to substitute any substring found, in every part it is found. We can optionally decide how many occurrences should be substituted.

VBA Replace Case Sensitivity

VBA Replace Function by default is case sensitive. You can change that behavior to case insensitive using an optional parameter (vbTextCompare). In that case, you must also define the starting position of the search.

You can also perform a case-insensitive Replace, by adding Option Compare Text to the top of your module:

 

VBA Replace Double Quotes

VBA Replace function can replace the double quotes character used to delimit the start and end of a string.

VBA Chr function can return a character from its number in the character set.

Or

Double quotes can be used inside VBA Replace function using “””” or VBA function Chr(34).

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! automacro

Learn More!!

VBA Replace Break Line in Cell

VBA Replace function can find the break line special character in a cell and remove it or replace it with a space character. The break line special character can be entered in a cell using the keyboard shortcut Alt+Enter and can be used in VBA code with its Character set number using VBA function Chr(10).

replace line break