Normalize text

To remove some of the natural complexity of text (strip punctuation, normalize case, remove extra spaces) you can use a formula based on the SUBSTITUTE function, with help from the TRIM and LOWER functions. 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.