SOLVED
Home

How do I Find/Replace or Filter with exact words/characters?

%3CLINGO-SUB%20id%3D%22lingo-sub-797147%22%20slang%3D%22en-US%22%3EHow%20do%20I%20Find%2FReplace%20or%20Filter%20with%20exact%20words%2Fcharacters%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797147%22%20slang%3D%22en-US%22%3E%3CP%3ERequirement%201%3A%20I%20want%20to%20be%20able%20to%20filter%20the%20cells%20to%20the%20letter%20%22a%22%20or%20%22is%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERequirement%202%3A%20I%20want%20to%20be%20able%20to%20Find%20and%20Replace%20the%20word%20%22is%22%20with%20should%20be%3C%2FP%3E%3CP%3EIssue%202%3A%20When%20I%20search%20for%20%22is%22%2C%20the%20words%20%22l%3CSTRONG%3Eis%3C%2FSTRONG%3Et%22%20and%20%22d%3CSTRONG%3Eis%3C%2FSTRONG%3Eplay%22%20show%20up%20and%20so%20the%20output%20is%20lshouldt%20and%20dshouldplay%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20possible%20without%20VBA%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-797147%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFilter%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797159%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20Find%2FReplace%20or%20Filter%20with%20exact%20words%2Fcharacters%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797159%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F389745%22%20target%3D%22_blank%22%3E%40MattC475%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20Find%20and%20Replace%20options%20check%20Match%20entire%20cell%20content%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F126374i12C8F825433EAEBC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIn%20column%20Filter%20you%20may%20select%20Text%20Filter%20-%26gt%3B%20Equals%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797183%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20Find%2FReplace%20or%20Filter%20with%20exact%20words%2Fcharacters%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797183%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%20-%20this%20does%20not%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20reason%20is%20that%20for%20that%20feature%20to%20work%20the%20cell%20must%20contain%20nothing%20but%20the%20word%20%22is%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20if%20I%26nbsp%3B%20have%20a%20cell%20F1%20with%20a%20value%20%22My%20name%20is%20Matt%22%20and%20I%20search%20for%20%22is%22%20and%20have%20%22match%20cell%20contents%22%20to%20true%2C%20then%20it%20will%20not%20find%20cell%20F1%20because%20the%20cell%20contents%20contains%20words%20other%20than%20%22is%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20cell%20just%20contained%20%22is%22%20and%20nothing%20else%20then%20your%20solution%20would%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20trying!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797190%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20Find%2FReplace%20or%20Filter%20with%20exact%20words%2Fcharacters%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797190%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F389745%22%20target%3D%22_blank%22%3E%40MattC475%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20wrap%20it%20by%20spaces%20searching%20for%20%22%20is%20%22.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-798605%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20Find%2FReplace%20or%20Filter%20with%20exact%20words%2Fcharacters%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-798605%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BUn-freaking%20believable!!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much!!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20small%20thing%2C%20for%20future%20reference%3A%20Is%20it%20possible%20to%20include%20punctuation%20e.g.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFind%20and%20replace%20%22is%22%20with%20%22should%20be%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3Evisual%20%3D%20visual%20%26gt%3B%20Pass%3C%2FLI%3E%3CLI%3Emistake%20%3D%20mistake%20%26gt%3B%20Pass%3C%2FLI%3E%3CLI%3Eis%20%3D%20should%20be%20%26gt%3B%20Pass%3C%2FLI%3E%3CLI%3Eis!%20%3D%20is!%20%26gt%3B%20Fail%3C%2FLI%3E%3C%2FUL%3E%3CP%3Eis%20it%20possible%20to%20include%20punctuation%20instead%20of%20doing%20find%20and%20replace%20for%20is!%2C%20is.%2C%20is%2C%20etc.%20..........%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E_______________________________________________________________________________________________________%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20future%20readers%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EMicrosoft%20Word%20has%20%22match%20whole%20word%20only%22%20Microsoft%20Excel%20does%20not%3C%2FLI%3E%3CLI%3ECopying%20from%20Excel%20to%20Word%20using%20feature%20in%20point%201.%20then%20copying%20back%20to%20Excel%20does%20not%20work%20as%20smoothly%20and%20you'd%20think%3C%2FLI%3E%3CLI%3EUsing%20the%20Find%2FSubstitute%2FRPT%20functions%20are%20not%20as%20simple%20as%20this!%3C%2FLI%3E%3C%2FOL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-798620%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20Find%2FReplace%20or%20Filter%20with%20exact%20words%2Fcharacters%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-798620%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F389745%22%20target%3D%22_blank%22%3E%40MattC475%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Matt%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAfraid%20not.%20You%20may%20use%20%22%20is%3F%22%20where%20the%20question%20mark%20means%20any%20single%20character.%20It%20finds%20%22%20is%20%22%2C%20%22%20is!%22%2C%20etc.%2C%20but%20the%20words%20as%20%22issue%22%20also%20will%20be%20found%20for%20substitution.%3C%2FP%3E%0A%3CP%3EOne%20option%20is%20to%20use%20Find%20Next%20and%20click%20Replace%20only%20if%20correct%20word%20was%20found.%3C%2FP%3E%0A%3CP%3EAnother%20option%2C%20if%20to%20replace%20in%20place%2C%20write%20some%20macro%20on%20VBA%20which%20will%20do%20the%20job.%3C%2FP%3E%0A%3CP%3EOne%20more%20option%20is%20to%20add%20helper%20column%2C%20if%20your%20texts%20are%20in%20column%2C%20and%20use%20formulas%20to%20change%20the%20text%20returning%20result%20into%20helper%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E
MattC475
Occasional Contributor

Requirement 1: I want to be able to filter the cells to the letter "a" or "is"

 

Requirement 2: I want to be able to Find and Replace the word "is" with should be

Issue 2: When I search for "is", the words "list" and "display" show up and so the output is lshouldt and dshouldplay 

 

Is this possible without VBA?

 

Thanks

5 Replies

@MattC475 

In Find and Replace options check Match entire cell content

clipboard_image_0.png

In column Filter you may select Text Filter -> Equals

Hi @Sergei Baklan  - this does not work.

 

The reason is that for that feature to work the cell must contain nothing but the word "is".

 

For example, if I  have a cell F1 with a value "My name is Matt" and I search for "is" and have "match cell contents" to true, then it will not find cell F1 because the cell contents contains words other than "is".

 

If the cell just contained "is" and nothing else then your solution would work.

 

Thanks for trying!

Solution

@MattC475 

You may wrap it by spaces searching for " is ". 

@Sergei Baklan Un-freaking believable!! 

 

Thank you so much!!!

 

One small thing, for future reference: Is it possible to include punctuation e.g.

 

Find and replace "is" with "should be"

 

  • visual = visual > Pass
  • mistake = mistake > Pass
  • is = should be > Pass
  • is! = is! > Fail

is it possible to include punctuation instead of doing find and replace for is!, is., is, etc. ..........?

 

_______________________________________________________________________________________________________

 

For future readers:

 

  1. Microsoft Word has "match whole word only" Microsoft Excel does not
  2. Copying from Excel to Word using feature in point 1. then copying back to Excel does not work as smoothly and you'd think
  3. Using the Find/Substitute/RPT functions are not as simple as this!

@MattC475 

Hi Matt,

 

Afraid not. You may use " is?" where the question mark means any single character. It finds " is ", " is!", etc., but the words as "issue" also will be found for substitution.

One option is to use Find Next and click Replace only if correct word was found.

Another option, if to replace in place, write some macro on VBA which will do the job.

One more option is to add helper column, if your texts are in column, and use formulas to change the text returning result into helper column.