Function used to replace a match with intended string. Excel replace and VBA (Visual Basic for Applications) replace serves different purpose. In this article we will see how replace function works in both Excel as well as VBA?
Replace number of characters start from position from the source string to new string.
=REPLACE(old_text, start_num, num_chars, new_text)
- old_text: refers to the source string or text on which you want to execute replace operation.
- start_num: an integer value indicating position from where the replace will start operation.
- num_chars: an integer value defines the length should be included while performing replace operation.
- new_text: refers to text which should be replaced in the old_text.
Above example source there are two examples, first formula performs replace from first position to two characters by replacing with “ee” and in second example we are replacing first character with “V” and the result:
Finds the match and replace with new string given in the criteria and returns a new replaced string. VBA Replace function is completely capable to serve same task as Excel Replace function does. Replace Function replaces all the occurrences of the text which is requested under Find criteria.
REPLACE(Expression, Find, Replace, Start, Count, Compare)
- Expression: refers to a string or text on which replace will take effect
- Find: refers to a string which needs to be searched through Expression
- Replace: refers to string which needs to be replaced with when Find criteria meets
- Start: an optional parameter, which can be passed if user wants to set the find start position within string
- Count: sets the boundary withing replace function hence find will not go beyond it.
- Compare: optional parameter to determine what kind of search you would like to perform. VBA offers inbuilt constants derived from VbCompareMethod as:
Public Sub VBAReplaceFunctionExample() Dim old_string As String 'string to be replaced old_string = "VBAOVERALL is the best site" 'Perform replace Dim new_string As String new_string = Replace(old_string, "VBAOVERALL", "INFOEXTRACT") 'print output Debug.Print new_string End Sub
INFOEXTRACT is the best site