How to Use VBA REPLACE Function in Excel – A Guide to Text Manipulation with 4 Examples

Excel VBA allows users to automate tasks, perform complex calculations, and manipulate data efficiently. Among a plethora of functions available in Excel VBA, VBA Replace Function stands out as a pioneering tool in text manipulation.

In this article, we will explore the following topics –

Download the Excel Workbook below to follow along and understand how to use VBA Replace Function in Excel –
download excel workbook VBA-Replace.xlsm

 

Introduction to VBA Replace

The VBA Replace function is a powerful tool used to manipulate text strings in Microsoft Excel. It is used to replace a set of characters from a text string with a new set of characters. The syntax of VBA Replace is –

Replace(Expression, Find, ReplaceWith, [ start, [ count, [ compare ]]])

where,

• Expression – The original text string where you want to perform the replacement.
• Find – The substring you want to find and replace within the text string.
• ReplaceWith – The string that will replace the original substring.
• Start (optional) – The position in the Expression from where the search should begin. By default, it starts from the first character (position 1).
• Count (optional) – The number of occurrences to replace. If omitted, all occurrences will be replaced.
• Compare (optional) – Specifies the type of string comparison. There are three options –
  • vbBinaryCompare (case-sensitive). Default
  • vbTextCompare (case-insensitive)
  • vbDatabaseCompare (used for database comparisons).

 

Example 1 – Simple Replacement

In this example, we will use VBA Replace to perform a simple text substitution. Our goal is to substitute the text “v4.0” with “v5.0”.

Follow the steps below to understand how to accomplish this –

STEP 1: Open the Workbook and press Alt + F11 to open the VBA editor.

STEP 2: Click on Insert > Module.

STEP 3: Write the following code –

Sub REPLACE_Ex1()
Range("A5").Value = Replace(Range("A2"), "v4.0", "v5.0")
End Sub

• Range(“A2”) – Range A2 contains the text string.
• v4.0 – The substring you want to find and replace.
• v5.0 – The string that will replace the original substring (i.e. v4.0).
• Range(“A5”).Value – The replaced text string will be displayed in cell A5.

STEP 4: Press the Run icon.

In cell A5, every instance of ‘v4.0’ has been successfully substituted with ‘v5.0’.

 

Example 2 – Case Insensitive Replacement

VBA Replace is a case-sensitive function but you can use the optional argument “compare” to make it work in a case-insensitive manner.

In this example, you want to replace all the occurrences of the word “John” with “Jack”. But as you can see, some of them are spelled in uppercase, some in lowercase, and some in mixed case.

If we use the same code as before, only the occurrence “John” will be replaced. The ones that are in uppercase or lowercase will stay unchanged.

Sub REPLACE_Ex1()
Range("A5").Value = Replace(Range("A2"), "John", "Jack")
End Sub

Now, let’s use the compare argument. It has three options – vbBinaryCompare, vbTextCompare, and vbDatabaseCompare. This argument specifies the type of string comparison to be used in the Replace function.

Here, “vbTextCompare” is used, which means the replacement will be case-insensitive.

Sub REPLACE_Ex1()
Range("A5").Value = Replace(Range("A2"), "John", "Jack", , ,vbTextCompare)
End Sub

All the occurrences of John have been replaced irrespective of their case.

 

Example 3 – Replace using Start Argument

Suppose you want to start replacing “John” with “Jack” from the 2nd position onwards. This can be executed using the Start argument in the VBA Replace function. The “Start” argument in the VBA Replace function allows you to specify the start position in the text string and will leave out any character before the start position.

This can be particularly useful when you want to skip certain occurrences of the substring or start replacing from a specific position.

The code that can be used is –

Sub REPLACE_Ex3()
Range("A5").Value = Replace(Range("A2"), "John", "Jack", 79)
End Sub

The total length of the characters before the 2nd occurrence (“John is a seasoned investment banker known for his excellent market analysis. “) is 78. So, the start parameter should be set to 79 since the second occurrence of ‘John’ begins at the 79th position.

Excel will begin searching the text string from the 79th position and replace all occurrences of John with Jack from the 79th position. The characters spanning from the 1st position to the 78th position will be removed from the result.

 

Example 4 – Replace using Count Argument

The Count argument in the VBA Replace function allows you to specify the number of occurrences of the substring you want to replace within the text string. If omitted, the default value is -1, which means, make all possible substitutions.

This is particularly useful when you want to limit the number of replacements made, rather than replacing all occurrences in the entire string.

Suppose you want to replace only 2 occurrences of “John” with “Jack”. The code that you can use will be –

Sub REPLACE_Ex4()
Range("A5").Value = Replace(Range("A2"), "John", "Jack", , 2)
End Sub

Only the 1st 2 occurrences of John have been replaced with Jack, and the remaining 2 occurrences remain unchanged.

The article discusses Excel VBA and its powerful VBA Replace function for text manipulation. It covers four examples of its usage: simple replacement, case-insensitive replacement, replacement using the Start argument, and replacement using the Count argument, demonstrating how to automate text substitutions in Microsoft Excel effectively.

Click here to learn more about For Loop in Excel.