Pinterest Pixel

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... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

How to Use VBA REPLACE Function in Excel - A Guide to Text Manipulation with 4 Examples | MyExcelOnline How to Use VBA REPLACE Function in Excel - A Guide to Text Manipulation with 4 Examples | MyExcelOnline

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).
See also  Convert Selection to Lower Case Using Macros In Excel

 

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”.

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

Follow the steps below to understand how to accomplish this –

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

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

STEP 2: Click on Insert > Module.

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

STEP 3: Write the following code

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

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.

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

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

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

 

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.

See also  Remove Characters at the Start Using Macros In Excel

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

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

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

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

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

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

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

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

 

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 –

See also  Protect All Cells With Formulas Using Macros In Excel

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

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.

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

 

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

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

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

See also  Highlight Duplicates Using Macros In Excel

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

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.

If you like this Excel tip, please share it
How to Use VBA REPLACE Function in Excel - A Guide to Text Manipulation with 4 Examples | MyExcelOnline How to Use VBA REPLACE Function in Excel - A Guide to Text Manipulation with 4 Examples | MyExcelOnline
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...