Allowing for Prefixes and Suffixes in Find and Replace

Written by Allen Wyatt (last updated September 10, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


Steven has a worksheet that contains quite a few part numbers, such as DCC2418R. He would like to change all the prefixes (always "DCC") to "RR" and all the suffixes (always "R") to "F". Thus, after the find and replace, DCC2418R would become RR2418F. Steven knows he can perform multiple Find and Replace operations to do the conversion. He suspects there is a way to do this in a single find-and-replace pass, but he's not sure how to go about it.

Your suspicion is incorrect, Steven—there is no way to do it in a single pass. Unlike Word (where it could be done in a single pass), Excel does not include the ability to use wildcards in replacements. That does not mean that you are out of luck. There are actually several ways to accomplish the task you need to perform.

Perhaps the easiest way is to use the Flash Fill tool. Let's assume that your part numbers are in column A, starting at A1. In cell B1, enter the correctly formatted part number. In cell B2, start to type the next correctly formatted part number. Flash Fill should kick into play. (See Figure 1.)

Figure 1. Flash Fill beginning its work.

Note the lightly shaded entries offered by Excel. This is the result of the Flash Fill feature. At this point, all you need to do is press Enter and you'll have your correctly formatted part numbers. If, for some reason, Flash Fill doesn't offer the transformations automatically, after you enter the corrected part number in cell B2, select the range of B1 through whatever is the last cell in column B. (For instance, select the range B1:B227.) Then press Ctrl+E, and Flash Fill should finish all the empty cells in the range you selected.

Of course, Flash Fill isn't available in all versions of Excel; it was first introduced in Excel 2013. If you don't have Flash Fill available, you could use a formula to do the conversions. The following, in cell B1, will give the correctly transformed part number of whatever is in cell A1:

=SUBSTITUTE(SUBSTITUTE(A1,"R","F",1),"DCC","RR",1)

The following formula will also work in cell B1:

="RR" & MID(A1,4,LEN(A1)-4) & "F"

If you prefer a macro approach, you could create one that examines a cell and makes the change if the prefix and suffix conditions are met. The following example, ReplacePartNum, does its work on a named range called "MyRange." (In other words, you need to define a named range called "MyRange" before you run the macro. The named range should contain all the cells you want examined and converted.)

Sub ReplacePartNum()
    Dim myRange As Range
    Dim c As Range
    Dim origText As String
    Dim firstBit As String
    Dim endBit As String
    Dim middleBit As String

    Set myRange = Range("MyRange")
    For Each c In myRange
        origText = c.Text
        firstBit = Left (origText, 3)
        endBit = Right (origText, 1)
        If firstBit = "DCC" And endBit = "R" Then
            middleBit = Mid (origText, 4, Len(origText) - 4)
            c.Value = "RR" & middleBit & "F"
       End If
    Next
End Sub

You should know that you can, if desired, utilize wildcards in your macros. A more thorough explanation of how to use wildcards within macros, can be found in this tip.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13479) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.

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

Generating a Table of Authorities

With the entries for your table of authorities marked throughout your document, you are ready to actually generate the ...

Discover More

Recording a Data Entry Time

When entering information in a worksheet, it is common to also note a date or time corresponding to the entry. There are ...

Discover More

Conditional Printing

If you need to make what Excel prints be based upon conditions in a worksheet, you'll love the information in this tip. ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Changing Default Search Settings

Excel provides some great tools for finding information in a worksheet or a workbook. Changing the default settings used ...

Discover More

Deleting Dates within Text Strings

Finding and replace dates contained within other text in a cell can be a bit tricky. This tip looks at some approaches to ...

Discover More

Replacing in Worksheets and Comments At the Same Time

If you need to replace information that may appear in cells, comments, and text boxes, your best bet is to use a macro. ...

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 8 - 3?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


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.