Allowing for Prefixes and Suffixes in Find and Replace

by Allen Wyatt
(last updated August 28, 2020)


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 do wildcard searches. 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:


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
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, and 2016.

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


Including a Data Table with Your Chart

Microsoft Graph allows you to display both your chart and data, if desired. All it takes is to make a simple change to a ...

Discover More

Enlarging Icons on the Quick Access Toolbar

Tired of squinting when you look at icons on the Quick Access toolbar? It seems there is no relief from the eye strain, ...

Discover More

Sorting ZIP Codes

Sorting ZIP Codes can be painless, provided all the codes are formatted the same. Here's how to do the sorting if you ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Limitations On Finding Characters

When you search for information in a worksheet, you expect Excel to return results that make sense. If you don't get a ...

Discover More

Replacing Characters at the End of a Cell

The Find and Replace capabilities of Excel can come in handy, but they can't accomplish all your replacement needs. One ...

Discover More

Replacing Tildes at the Beginning of a Cell

Replacing a specific character (such as a tilde) seems a simple task, until you need to replace it only in a certain ...

Discover More

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

View most recent newsletter.


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 nine minus 5?

2020-08-29 10:40:12

Rick Rothstein

The articles macro seems overly long to me. Here is a shorter version that should work...

Sub ReplacePartNum()
Dim CVal As String, C As Range
For Each C In Range("MyRange")
CVal = C.Value
If CVal Like "DCC*R" Then C.Value = "RR" & Mid(CVal, 4, Len(CVal) - 4) & "F"
End Sub

2020-08-28 08:18:29

Jennifer Thomas

This is interesting, but seems a bit complicated - I would have split the column into prefix-number-suffix, (using width delineation), replaced the prefix and suffix values, then concatenated the three columns back together and copy-paste as values to make it stick. But I'll try the formula ... if I can remember it!

2017-07-17 17:05:28

Dennis Costello

I believe Allen misspoke in the hint: Excel does offer wildcard searches - what it can't do is wildcard replace, which is what Steven is looking for. A wildcard replace would, e.g., replace the syndrome "DCC*R" with "RR*F". Excel doesn't recognize the * wildcard in the Replace with text as the place to put the wildcard text that was "found" by the * in the search syndrome, so all the cells would end up being filled with "RR*F".

A feature of the "two search-and-replace" manual approaches is that a part number of the form DCC5670R-01 would become RR5670F-01. Whether that's a desired outcome or an error, of course, is for Steven to decide. The macro would of course not do that, because it requires the last character to be an R ... but ... it would turn DCC5670R-01R into RR5670R-01F.

As with all such tasks, it all comes down to what the data looks like.

Meanwhile, I haven't looked into the relational expression capability in VBA - could that be used to create a simpler macro that accomplishes this task? Seems like it should... and I'm looking forward to an excuse to spend the time to learn how to use REs.

2016-10-11 05:44:11

Michael (Micky) Avidan

"Experience brings wisdom & knowledge".
Why don’t you just try it...?
Michael (Micky) Avidan
“Microsoft®” MVP – Excel (2009-2017)

2016-10-10 10:33:13


After you've done the first one, couldn't you just copy the formula to the rest?

2016-10-09 08:32:43

Michael (Micky) Avidan

@Andrew Day,
Beside, your formula, being a little longer than the second formula suggested in this tip, all formulas will end with the same result also for: DCC7892418R
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2017)

2016-10-09 07:27:39

Andrew Day

It can be done in a single formula - a bit complicated but nesting the text manipulation features of Excel:


The first bit inside the bracket strips out the first three characters and replaces them with RR, using the length of the string to work out what bit to keep. And then the outer formula takes the last character off, again using the length of the string before the very last bit adds an F.

For the second substitution you have to subtract 2 from the length because you have shortened the string by 1 in the first substitution.

This has the advantage that if any of the product codes are not standard lengths, they will still get the first three characters replaced by RR and the last replaced by F.

2016-10-09 01:38:19


"In a single pass" means "using a single find-and-replace operation." Your approach will work fine, but it requires two passes--one to replace the R and then one to replace the DCC.


2016-10-08 10:19:14

Leslie Scott

I am confused by the statement: 'there is no way to do it in a single pass.'

Unless I misunderstand the exercise, which I don't believe that I do, Excel's search and replace can easily accomplish this task.

If other contents of the spreadsheet might be affected by the replacement of 'DCC' with 'RR' or of 'R' with 'F', then be sure to highlight the target cells first. Then, using the Find and Replace function, replace 'R' with 'F' first. Next replace 'DCC' with 'RR'. Done.

If that doesn't work it can also be easily handled without using a by creating a new column with a formula that transforms the old part number to the new format, then copy and replace values to the Part Number column.

Did I miss the mark somehow?

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

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.