Replacing Only Whole Words in Excel

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 correctly 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, 2013, 2016, 2019, and 2021.