Context
There may be times when you need to remove some of the variability of text before other processing. One example is when you want to count specific words inside larger text strings. Because Excel doesn’t provide support for regular expressions, you can’t construct precise matches. For example, if you want to count how many times the word “fox” appears in a cell, you will end up counting “foxes”. You can look for “fox " (with a space) but that will fail with “fox,” or “fox.” One workaround is to simplify the text first with a formula in a helper column, then run counts on the simplified version. The example on this page shows one way to do this. In the version below, line breaks have been added for readability, and to make it easier to edit replacements. Excel does not care about line breaks in formulas, so you can use the formula as-is. After all substitutions are complete, the result is run through TRIM to normalize spaces, then the LOWER function to force all text to lowercase. Note: You’ll need to adjust the actual replacements to suit your data.
Adding a leading and trailing space
In some cases you may want to add a space character to the start and end of the cleaned text. For example, if you want to count words precisely, you may want to look for the word surrounded by spaces (i.e. search for " fox “, " map “) to avoid false matches. To add a leading and trailing space, just concatenate a space (” “) to the start and end: Where “formula” is the longer formula above.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
