Excel Replace vs VBA Replace Function with example

Replace

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?

Excel Replace

Replace number of characters start from position from the source string to new string.

Syntax

=REPLACE(old_text, start_num, num_chars, new_text)

Parameters

1. old_text: refers to the source string or text on which you want to execute replace operation.
2. start_num: an integer value indicating position from where the replace will start operation.
3. num_chars: an integer value defines the length should be included while performing replace operation.
4. new_text: refers to text which should be replaced in the old_text.

Example

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:

Output

VBA Replace

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.

Syntax

REPLACE(Expression, Find, Replace, Start, Count, Compare)

Parameters

1. Expression: refers to a string or text on which replace will take effect
2. Find: refers to a string which needs to be searched through Expression
3. Replace: refers to string which needs to be replaced with when Find criteria meets
4. Start: an optional parameter, which can be passed if user wants to set the find start position within string
5. Count: sets the boundary withing replace function hence find will not go beyond it.
6. Compare: optional parameter to determine what kind of search you would like to perform. VBA offers inbuilt constants derived from VbCompareMethod as:
   1. vbBinaryCompare
   2. vbDatabaseCompare
   3. vbTextCompare

Example

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

Output

INFOEXTRACT is the best site

Next >> Error checking a complete reference in Excel

Share on Facebook

Tweet