Find, match whole word only workaround

Question

I just noticed, there is no option in Excel Find, to "match whole word only" (I am not talking about "match entire cell content") e.g. searching for "part" should not match "partner". MS Word has this option (check screenshot from MS Word).

Is there a workaround for this in Excel?

Answer 1

As pointed out by Scott, my original answer was wrong.

Because of the multitude of ways you can define a word boundary, this will be hard to do with a forumla. I guess that is why \b (word boundary) exists in regular expressions. If only we could use them in Excel's find / replace dialog.

Install this Excel add-in, http://www.codedawn.com/excel-add-ins.php click on the new Find & Repalce RegEx button (or menu option on 2003) and use this search term \bword\b, untick/tick match case and hit Find.

It is important the b's are lowercase and you will have to learn a bit about regular expressions (recommend this site) if you want to search for something involving punctuation but it's ok to put any standard letter/number between the \b tags if that's all you want to search for.

I think I covered most cases with this test:

Answer 2

Formula-wise you can search for "Word" (non case-sensitive) with this formula to get TRUE for the exact word only, assuming no punctuation

=ISNUMBER(SEARCH(" Word "," "&A1&" "))

That would return TRUE for text like

A word to the wise or Grease is the word

and correctly FALSE for Sword of Damocles.....but you'd also get FALSE for something like

My Word! because the exclamation mark negates the match

To cope with punctuation, you can use multiple SUBSTITUTE functions, e.g. to ignore comma, semi-colon and exclamation mark you can use this version

=ISNUMBER(SEARCH(" Word "," "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,",",""),";",""),"!","")&" "))

Answer 3

The elegant RegEx solution didn't work in Excel 2010, but you can use MS Word for a simple workaround:

1. Select the range in Excel.

2. Copy it.

3. Paste it into a new blank MS Word document.

4. Use Word's find-and-replace feature, but select "Find whole words only" on the text you're searching for.

5. When you're done replacing, copy that whole table from Word.

6. Go back to Excel. The range is still selected. Just paste your copied table.

Answer 4

I've found a solution for that problem.

You must create a function that you're going to call after you've made your standard search and found 1 result.

The function is going to check if the word is whole or note. Here is the code of the function:

It checks the char before and after the expression found and if it's NOT a letter (accented or not) it returns true, else false.

Function WholeWord(ByVal text As String, ByVal expression As String) As Boolean

Dim aux1 As Integer: aux1 = 0
Dim aux2 As Integer: aux2 = 0


Dim condition1 As Boolean: condition1 = False
Dim condition2 As Boolean: condition2 = False

aux1 = InStr(1, text, expression, vbTextCompare)

If aux1 = 1 Then
condition1 = True
Else
If UCase(Mid(text, aux1 - 1, 1)) Like "[!A-ZÂÊÎÔÛÁÉÍÓÚÇÃÕÀÈÌÒÙÄËÏÖÜ]" Then
condition1 = True
End If
End If

aux2 = aux1 + Len(expression)

If aux2 = Len(text) + 1 Then
condition2 = True
Else
If UCase(Mid(text, aux2, 1)) Like "[!A-ZÂÊÎÔÛÁÉÍÓÚÇÃÕÀÈÌÒÙÄËÏÖÜ]" Then
condition2 = True
End If
End If

If condition1 = True And condition2 = True Then
WholeWord = True
Else
WholeWord = False
End If

End Function

Answer 5

When putting in the parameters in the "Find" section, just include a space before or after the word. In my case, " Van" kept "SAVANA" from changing, and "Man " would prevent "Mankind" from changing.