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

Wildcards in 'Replace With' Text

Written by Allen Wyatt (last updated January 22, 2025)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021


9

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:

=REPLACE(A1,2,1,"a")

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. 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 option. If there is more than one version of the reference available, pick the latest version.

Enabling this reference allows you to create REGEX objects. Technically you could leave it turned off, depending on how you want binding to occur in your macros. A discussion on the pros and cons of late vs. early binding is beyond the scope of this tip, however, so just go ahead and turn on the REGEX reference.

REGEX 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
        SearchNReplace1 = reg.Replace(TestString, ReplaceString)
    Else
        SearchNReplace1 = 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:

=SearchNReplace1("^ab.*de$","^ab","aa",A1)

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 would prefer not to use REGEX expressions in VBA for some reason, 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
    Next
    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.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11170) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021. You can find a version of this tip for the older menu interface of Excel here: Wildcards in 'Replace With' Text.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Where Are Word's Settings Stored?

Ever wonder where Word stores all its settings and configuration information? There are only three places where this ...

Discover More

Entering a Degree Sign

One of the more common symbols that people need to use in their writing is the degree symbol, typically used after a ...

Discover More

Moving a Table Row

Want to move a row in a table very easily? You can do so by using the same editing techniques you are already using.

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Finding and Replacing with Subscripts

Want to use Find and Replace to change the formatting of a cell's contents? You would be out of luck; Excel won't let you ...

Discover More

Finding and Replacing in Text Boxes

Finding and replacing information in a worksheet is easy. Finding and replacing in other objects (such as text boxes or ...

Discover More

Find and Replace in Headers

Using Find and Replace is something quite routine in Excel, as it easily allows you to find and replace information in ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 7 - 7?

2025-02-03 12:25:02

J. Woolley

Re. my most recent comment below, the RegExSubstitute function in My Excel Toolbox is similar to Excel 365's REGEXREPLACE function. RegExSubstitute accepts a single Text input and returns a single result, but REGEXREPLACE can accept a multi-cell range or array as input and return a dynamic array result. A similar result can be produced using My Excel Toolbox's ForEachCell function, which is described in my comment dated 2025-01-20 here:
https://excelribbon.tips.net/T011725#comment-form-hd
The formula used in Figure 1 of my most recent comment below is
    =REGEXREPLACE(I2:K9, "^ab(.*de$)", "aa$1")
The equivalent formula using ForEachCell and RegExSubstitute is
    =ForEachCell(I2:K9, "RegExSubstitute(@, ""^ab(.*de$)"", ""aa$1"")")
For RegExSubstitute, see https://tips.net/T009392#comment-form-hd
Also, see https://sites.google.com/view/MyExcelToolbox/


2025-02-01 15:01:19

J. Woolley

@Tomek
Using the cell data in your Figure 1, the Excel 365 formula illustrated in my figure (see Figure 1 below) produces a dynamic array containing the same results as your Figure 2, but not in situ. This formula replaces "ab" at the start of text, but any "ab" in the middle remains undisturbed.
Also, here is a simplified version of the Tip's SearchNReplace1:

Public Function SearchNReplace3(Text As String, _
    Pattern As String, Replacement As String)
    Dim reg As New RegExp
    reg.IgnoreCase = True
    reg.MultiLine = False
    reg.Pattern = Pattern
    If reg.Test(Text) Then
        SearchNReplace3 = reg.Replace(Text, Replacement)
    Else
        SearchNReplace3 = Text
    End If
End Function

And here is a formula to replace the first two characters of text like "ab*de" in cell A1 with "aa" and return the result:
    =SearchNReplace3(A1, "^ab(.*de$)", "aa$1")
Notice $1 in the Replacement string represents the parenthesized part of the Pattern string that was matched in the Text string. If A1 contains "abode" the formula returns "aaode" and if A1 contains "ABODE" the formula returns "aaODE".

Figure 1. 


2025-01-31 15:41:24

Tomasz Dluzniewski

@Kiwerry
Thank you for summarizing my approach so clearly. This may make people no longer scared of my multi step procedure.


2025-01-30 04:27:58

Kiwerry

@ Tomek:
"The trick is in defining what you want to look for and search for all occurrences of it, but not do any replacement at first."
I.e. splitting the process into two stages - getting Excel to select the required cells first, so that the replacement only affect those cells - thanks for the ingenious suggestion.


2025-01-30 01:24:46

Tomek

re: 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:

Actually, there is a way to do this without resorting to macros:
The trick is in defining what you want to look for and search for all occurrences of it, but not do any replacement at first.
Before starting, select the area on which you want to do the replacement. If you select a single cell the whole sheet will be affected.
Press Ctrl+H to open the Search and replace Dialog Box then:
1. In the Find what: box enter "ab*de" (without quotes) and check match entire cell contents.
2. Click on Find All
3. Press Ctrl+A. This will select all the cells matching the Find pattern and also will highlight them in the list at the bottom of the Dialog box
4. in the Find what: box leave only "ab", and un-check the Match entire cell content.
5. In the Replace with: box enter "aa"
6. Click on replace all.

The above will do the replacement only in the selected cells, so only those that matched the initial find pattern. Only the "ab" will be replaced leaving the rest unchanged.

As "ab" is the start of the cell content and "de" is the ending (because the search matches entire cell contents), any occurrences of "ab" or "de" only in the middle of the cell contents will not be found. However if the cell contents starts with "ab" and ends with "de" but also has "ab" in the middle that extra "ab" will also be changed to "aa". This will show up however in the popup indicating more replacements than the count of the cells found in step 2. Such situation should be rare though and only occasionally require manual correction.

Similarly, you can change only the part after the * in the Find pattern.
(see Figure 1 below) (see Figure 2 below) for screenshots.

Figure 1. After step 3

Figure 2. After step 6


2025-01-24 04:27:42

Kiwerry

@J. Woolley: Thanks for reminding me. My memory is no longer as sharp as it used to be.


2025-01-23 15:58:50

J. Woolley

@Kiwerry
See my most recent comment here: https://excelribbon.tips.net/T009392#comment-form-hd
Also, see https://support.microsoft.com/Search/results?query=REGEX


2025-01-22 04:48:43

Kiwerry

Thank you Allen. Could you please follow this up with an article on the three new REGEX functions available in Exce365 at some stage?


2021-07-17 11:58:20

Willy Vanhaelen

Anne-Mie's request is to replace some characters in the first part of the string ("ab" in the example) while she wants to do it only when the last part meets some characters ("de" in the example) but she doesn't want to change those. That's what the first macro does. But the second macro provides the possibility to change the last part as well what makes it rather complicated.

I simplified this macro to do only what Anne-Mie asks with this result:

Sub SearchNReplace3()
Dim rcell As Range, sTemp As String

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

For Each rcell In Selection
sTemp = rcell.Value
If Left(sTemp, Len(cFindInitial)) = sFindInitial _
And Right(sTemp, Len(sFindFinal)) = sFindFinal Then
rcell = Replace(rcell, sFindInitial, sReplaceInitial, 1, 1)
End If
Next
End Sub

(see Figure 1 below)

Figure 1. 


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.