Replacing Only Whole Words in Excel

by Allen Wyatt
(last updated June 27, 2015)

8

Gary wants to do a find and replace for an abbreviation like "sys" and replace it with "systems." The problem is that if he replaces all occurrences, it transforms existing instances of "system" into "systemstem." Gary could examine each occurrence and replace individually, but that would take forever. He wonders if there is a way to make Excel replace only "whole word" occurrences of "sys" with "systems" as there is in Word.

Those familiar with Excel's Find and Replace capabilities might think, at first blush, that you could use the "Match Entire Cell Contents" setting in order to do the work. That only works, however, if the abbreviation "sys" is the only thing in a cell. In Gary's situation, that isn't the case. He has phrases and entire sentences that contain "sys" (as in "sys admin" or "A sys check is needed"). Thus his request for something like Word's "whole word" setting.

With that in mind, there are a few things you can try; the solution that is best for you will depend on the nature of the text in your worksheet. For instance, if the only probable confusion point is the word "system," then it will be easiest to simply replace "system" with something unique, such as "{[]}". (I tend to like the braces and brackets because they are unique in most data.) Then do your replacement of "sys" with "systems" and finally replace "{[]}" with "system."

If there are other confusion points besides "system," then you can resort to adding spaces in your Find What text. In other words, search for " sys " (with the spaces) and replace it with " systems " (again, with the spaces). This will find all occurrences correct as long as sys isn't followed by a punctuation mark, doesn't occur at the beginning of a cell, and doesn't occur at the end of a cell.

Of course, you could perform additional searches to get at some of this info. For instance, you could search for "sys " (space only at the end) to find occurrences at the beginning of a cell. It would do you no good to search for " sys" (space only at the beginning) because that would match any occurrence of "system" preceded by a space. In sequential searches you could also search for "sys" followed by a period, comma, question mark, exclamation mark, semi-colon, etc.

If you prefer to use a macro, the following is one example of how you could approach the problem. The macro prompts you for what you want to find and what you want to replace it with.

Sub ReplaceOnlySpecifirdWord()
    Dim c As Range, rng As Range, rngArea As Range
    Dim vFind As String, vReplace As String
    Dim v As Variant
    Dim arrSplit As Variant
    Dim s As String
    Dim i As Integer, n As Long
    Dim b As Boolean
    Const csDELIMITER = " "

    On Error Resume Next
    ' Reference Constants only
    Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
    If rng Is Nothing Then
        MsgBox "There are no constants on active sheet...", vbExclamation
        Exit Sub
    End If
    On Error GoTo 0

    ' Get Find and Replace strings
    v = InputBox(Prompt:="Please enter String to be replaced.")
    If v <> "" Then
        vFind = v
    Else
        MsgBox "Wrong entry; app is going to be terminated.", vbExclamation
        Exit Sub
    End If

    v = InputBox(Prompt:="Please enter Replace String.")
    If v <> "" Then
        vReplace = v
    Else
        MsgBox "Wrong entry; app is going to be terminated.", vbExclamation
        Exit Sub
    End If

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    n = 0
    For Each rngArea In rng.Areas
        For Each c In rngArea.Cells
            b = False
            arrSplit = Split(c.Value, csDELIMITER)
            For i = LBound(arrSplit) To UBound(arrSplit)
                If arrSplit(i) = vFind Then
                    arrSplit(i) = vReplace
                    n = n + 1
                    b = True
                End If
            Next i
            If b Then
                s = vbNullString
                For i = LBound(arrSplit) To UBound(arrSplit)
                    s = s & arrSplit(i)
                    If i <> UBound(arrSplit) Then
                        s = s & csDELIMITER
                    End If
                Next i
                c.Value = s
            End If
        Next c
    Next rngArea

    Application.Calculation = xlCalculationAutomatic
    MsgBox "Replaced " & n & " words...", vbInformation
End Sub

The macro does its work by finding all the words in each cell (in turn). If the word matches what you are looking for, then it is replaced. The cell contents are then put back together.

There is a drawback to this approach: The matching is very literal. This means that "sys" will match "sys", but won't match "Sys", "SYS", or "sys" followed by a punctuation mark. (The macro uses spaces as a delimiter between words.)

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13391) applies to Microsoft Excel 2007, 2010, and 2013.

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

Counting Unique Values with Functions

Using Excel to maintain lists of information is not unusual. When working with the list you may need to determine how many ...

Discover More

Maintaining Text Formatting in a Lookup

Want to maintain the formatting used in one cell when you use formulas to reference that text in another cell? The answer is ...

Discover More

Resetting All Shortcut Keys

At some point you might want to wipe out all the custom shortcut keys you've created in Word. This is easy to do by following ...

Discover More

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!

More ExcelTips (ribbon)

Finding Boolean Values

Excel worksheets can contain all sorts of data. One thing you might store in a worksheet is a range of Boolean (TRUE or ...

Discover More

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

Discover More

Searching for Leading Apostrophes

Take a look at the Formula bar when you select a cell that contains text, and you may see an apostrophe at the beginning of ...

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}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 8Mpixels. 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 - 0?

2017-06-22 14:54:15

danny

@Locke,
can you make it so is not case sensitive?
replace house
should replace House, hOuse, house


2017-06-22 14:52:34

Danny

@Locke,

You are missing "\" for the reg pattern

you have:
Regex.Pattern = "b" & vFind & "b"

it should include the \b

Regex.Pattern = "\b" & vFind & "\b"


2015-07-10 13:40:53

Sandra Pearce

I'm no expert. But I would use the original "easiest" solution, except... Why not first replace "system" with "sys", and then replace "sys" with "system" and lose the "{[]}" step?


2015-06-30 11:42:18

Locke Garmin

@Frank

The "b" is a character anchor in Regular Expressions that will make sure the word you are matching has a non-alphanumeric character next to the alphanumeric character in vFind, without matching (and ultimately replacing in this case) the previously stated non-numeric character. Putting "b" on either side of vFind will make sure only to match "sys" words and not the 'sys' in "system" (because the 't' in "system" is an alphanumeric character).

Thanks for the feedback! :)


2015-06-29 20:15:20

Petros

The {[]} trick sounds cool! It is always prudent to run a FIND (without REPLACE) to get an idea of what your data looks like.

I believe in most cases, a suffix or prefix space will make the key difference.

Undo is available with Excel's Find-Replace. So a replacement goes wrong, it can be undone, given that a check is done. Here is how to change Undo settings

http://www.spreadsheet1.com/excel-options-addin.html

Undo is not available when running VBA. Here is how to maintain spreadsheet version control at a click of the save button (freeware app)

http://www.spreadsheet1.com/spreadsheet-version-control.html


2015-06-29 05:01:51

Frank

@Locke Garmin

Unfortunately, your code does not replace vFind but "b"


2015-06-29 00:33:22

Bob Beechey

The macro looks a little old fashioned. In recent versions of Excel, we can use the Join function rather than manually stitching up the replacement string. So, removing cumbersome and unnecessary error checking and using Join we have:

Sub ReplaceOnlySpecifirdWord()
Dim cell As Range, rng As Range, rngArea As Range
Dim vFind As String, vReplace As String
Dim arrSplit() As String
Dim found As Boolean
Const csDELIMITER = " "

Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
If Not rng Is Nothing Then

vFind = InputBox(Prompt:="Please enter String to be replaced.")
vReplace = InputBox(Prompt:="Please enter Replace String.")

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each rngArea In rng.Areas
For Each cell In rngArea.Cells
found = False
arrSplit = Split(cell.Value, csDELIMITER)
For i = LBound(arrSplit) To UBound(arrSplit)
If UCase(arrSplit(i)) = UCase(vFind) Then
arrSplit(i) = vReplace
found = True
End If
Next i
If found Then
cell.Value = Join(arrSplit, csDELIMITER)
End If
Next cell
Next rngArea

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End If
End Sub


2015-06-28 23:04:16

Locke Garmin

I think that Regular Expressions would be a better solution than string/array manipulation. Particularly because Regular Expressions handle "whole words" natively. If you replace the code in the for each block in this tip with the code below and it will be able to handle punctuation in cells.

Dim Regex As Object
Set Regex = CreateObject("VBScript.Regexp")

Regex.Global = True
Regex.Pattern = "b" & vFind & "b"

For Each rngArea In rng.Areas
For Each c In rngArea.Cells
If Regex.Test(c.Value) Then
n = n + Regex.Execute(c.Value).Count
c.Value = Regex.Replace(c.Value, vReplace)
End If
Next c
Next rngArea

You'll also want to add in a check for punctuation characters in the v variable and end the program if it finds anything in the string that isn't a letter or number so you don't confuse the Regex engine with improper user input.

I would change the following lines:
' Get Find and Replace strings
v = InputBox(Prompt:="Please enter String to be replaced.")
If v <> "" Then

To this:
' Get Find and Replace strings
v = InputBox(Prompt:="Please enter String to be replaced.")
If Not (v = "" Or v Like "*[!A-Za-z0-9]*") Then


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.