Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. 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.
Written by Allen Wyatt (last updated July 17, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11170) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Wildcards in 'Replace With' Text.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
If you need to replace information that may appear in cells, comments, and text boxes, your best bet is to use a macro. ...
Discover MoreDo you often want to search through a worksheet by column rather than by row? Excel defaults to searching by row, of ...
Discover MoreWant 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 MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments