Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

Word - 'Find whole words only' example

share|improve this question
    
Recently flogged to death here. –  Scott Sep 24 '12 at 23:10
    
@Scott So did there turn out to be entire cell content or potentially partial (but surrounded by spaces)???? –  pnuts Sep 25 '12 at 1:27
    
I have added more details in the question, please check. –  Kiranshell Sep 25 '12 at 2:22
    
@Scott I have added more details in the question, please check. –  Kiranshell Sep 25 '12 at 2:45

5 Answers 5

up vote 2 down vote accepted

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: test image

share|improve this answer
    
But if I’m searching for “BROW”, your workaround (specifically, the search for “ BROW”) will match “The quick brown fox” or “I like brownies.” Conversely, a search for “FAR” will not match “Far, a long long way to run.” –  Scott Sep 25 '12 at 17:48
    
Thanks Scott. I've updated my answer. –  Luke Sep 25 '12 at 21:55
    
Thanks Luke, I tired a few and it works, I am wondering why Microsoft has this option in word but not in Excel. –  Kiranshell Sep 26 '12 at 18:19

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,",",""),";",""),"!","")&" "))

share|improve this answer

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
share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
    
That's horrific... While I agree this works, I believe the asker is already aware of this possibility and was looking for a more elegant solution in Excel. –  Will.Beninger May 24 '13 at 21:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.