Pinterest Pixel

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

John Michaloudis
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.
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

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

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.

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 –

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.

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
Founder & Chief Inspirational Officer at

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 MyExcelOnline Academy Online Course.

See also  Add Custom Header Using Macros In Excel

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

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