Excel.Tips.Net ExcelTips (Menu Interface)

Wildcards in 'Replace With' Text

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Wildcards in 'Replace With' Text.

Anne-Mie realizes that she can use wildcards (*?) to search in Excel, but she wonders if she can use wildcards in the replace string. For instance, she would like to search for "ab*de" and replace it with "aa*de", where the asterisk represents any number of characters, or none at all.

The short answer is that there is no way to do this in Excel, as described. If you only wanted to convert the second character of a text value from "b" to "a", then that can be done rather easily:


This, however, is probably not what you want to do; you want a way to use wildcards in the "replace with" text. The technical term for doing such string replacements is called REGEX, which is short for Regular Expressions. REGEX started with languages like Perl but was so powerful that many other programming languages added it on.

The VBA used in Excel is no exception. REGEX was added to Visual Basic 6.0, which means that it made its way to Excel's VBA in Excel 2003. The first step in using REGEX is to turn it on. You do this in the VBA Editor by choosing Tools | References and then making sure there is a check mark next to the Microsoft VBScript Regular Expressions 5.5 option.

Enabling this reference allows you to create REGEX objects. These objects possess a Test method and a Pattern property. This means that you set the Pattern property, and then the Test method checks to see if the pattern exists. A REGEX object also has a Replace method, which is used to do replacements.

Before proceeding, it is important to understand that regular expressions can get very complex and, well, "geeky." There is no way around it; how to work with regular expressions has been the subject of entire books. Fortunately, for the purposes of this tip, the expressions are rather simple in nature. In this case we'll use the pattern "^ab.*de$". This pattern refers to a word that starts (indicated by the ^) with "ab" followed by an arbitrary expression (indicated by *) consisting of at least one character (indicated by the period) and ending (indicated by the $) with "de".

Here is the code that implements the use of the REGEX object to do the actual replacements.

Public Function SearchNReplace1(Pattern1 As String, _
  Pattern2 As String, Replacestring As String, _
  TestString As String)
    Dim reg As New RegExp

    reg.IgnoreCase = True
    reg.MultiLine = False
    reg.Pattern = Pattern1
    If reg.Test(TestString) Then
        reg.Pattern = Pattern2
        SearchNReplace = reg.Replace(TestString, ReplaceString)
        SearchNReplace = TestString
    End If
End Function

To use this macro, start with the strings you want to change in column A. Assuming that the first string is in cell A1, you could place the following into another cell in order to get the changed text:


This tells the macro that the pattern you want to look for is "^ab.*de$" (the first parameter), and that you want to replace "^ab" with "aa". This formula can be pasted down the column, and you end up with a conversion of column A where the string "ab*de" is replaced by "aa*de".

If you are using an older version of Excel that does not allow you to create REGEX objects, or if you would prefer not to do so, then you can create a macro that will simply step through a group of selected cells and look for any cell that begins with "ab" and ends with "de", and then replaces the beginning part with "aa".

Sub SearchNReplace2()
    Dim sFindInitial As String
    Dim sReplaceInitial As String
    Dim iLenInitial As Integer
    Dim sFindFinal As String
    Dim sReplaceFinal As String
    Dim iLenFinal As Integer
    Dim sTemp As String
    Dim rCell As Range

    sFindInitial = "ab"
    sReplaceInitial = "aa"
    sFindFinal = "de"
    sReplaceFinal = "de"

    For Each rCell In Selection
        sTemp = rCell.Value
        iLenInitial = Len(sFindInitial)
        iLenFinal = Len(sFindFinal)
        If Left(sTemp, iLenInitial) = sFindInitial And _
            Right(sTemp, iLenFinal) = sFindFinal Then
            sTemp = Mid(sTemp, iLenInitial + 1)
            sTemp = Left(sTemp, Len(sTemp) - iLenFinal)
            sTemp = sReplaceInitial & sTemp & sReplaceFinal
            rCell.Value = sTemp
        End If
    Set rCell = Nothing
End Sub

To use this routine, simply select the cells you want to change, and then execute the macro. You should also make changes to the sFindInitial, sReplaceInitial, sFindFinal, and sReplaceFinal variables, as needed.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3303) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Wildcards in 'Replace With' Text.

Related Tips:

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 5+3 (To prevent automated submissions and spam.)
           Commenting Terms

Comments for this tip:

Robin    25 Oct 2016, 15:06
@Dave Wilson from June 2015: I have done search & replace sequences like this before, with the first step being to replace "=" with " =", (replace equal with space-equal), and then reverse that as the last step.

This changes the formula into a text string, which then isn't affected by parenthesis imbalance. When you have the text string the way you want it, then change it back to a formula by removing the space in front of the equal sign.

Chris    05 Aug 2015, 14:16
@BenK - that only appears to apply to Word. When you follow the instructions in Excel, there is no "Use Wildcards" option
Dave Wilson    09 Jun 2015, 08:36
I want to change about 1200 formulae on a sheet to include the LEFT function as below


It can be done with 2x find/replaces, which isnt allowed because the first replace upsets the parenthesis balance

1st replace: cellcolor = LEFT((cellcolor
2nd replace: TRUE)) = TRUE)),2))

Can this tip do it in one? What expression would I type?

Tony    15 Apr 2015, 15:57
I have a slightly different problem. I'm trying to change the following -
1.49 TB
1.68 TB
Is there a way to do a search on a row and change all to numerical values, deleting the "TB" and shifting the decimal? Thx!
Gregg    09 Apr 2015, 14:58
Works perfectly!!!!


Just changed .value per .Formula because I needed to modify the formulas.
BenK    04 Dec 2014, 21:05
Office now supports backreferences in the replace text, ie you can group parts of the search string (with wildcards) using brackets, and refer to them in the replace string using \1 \2 etc.

Jos    11 Apr 2014, 04:36
I can't get it to work
This is the text (tekststr) :
It must be altered to:

This was the expression
SearchNReplace1(";*>$", "*", "", tekststr)

(I dont want to use the phrase Woonplaats because there are more simular tags to alter)

What do i wrong?
Can you heklp me??
Peter    04 Nov 2011, 18:23
Yip, perfect, just what I was looking for, Thank you

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2017 Sharon Parq Associates, Inc.