The SUBSTITUTE function is incredible for cleaning up text. You can replace a string of text with whatever you’d like, and if the text isn’t found you don’t get an error; It just returns the original string unharmed.
The CHAR (as in “character”) function is a pretty cool weapon as well. You can specify which characters you want to target, even if they’re weird characters, numbers, letters, or paragraph breaks. You’ll see why this is important shortly.
Examples of Random Characters and their character numbers
- Lowercase “t” = Character Number 116
- Uppercase “T” = Character Number 84
- Paragraph Break = Character Number 10
These character numbers allow Excel to target very specific parts of text strings.
Remove the second instance of a URL from a block of text
Imagine we don’t need to show two instances of the same url in one cell. Let’s erase the second one using substitute
Here’s the formula:
Within E2, we’re substituting the string “DunderMifflin.com” with double quotes – “”. That means we’re essentially replacing the url with nothing. The 2 you see at the end of the formula signifies that we’re replacing the 2nd instance of the url and NOT the first.
While it may look like we’re done, you’ll can see a extra paragraph break that we haven’t replaced with anything yet. Let’s use our friend CHAR() to do that.
Looks Good! Let’s analyze the code:
This formula is close, but CHAR(10) is what Excel knows to mean a paragraph break. Using the ampersand sign is a quick way to concatenate two values together. So instead of looking for the url by itself, we’re looking for two paragraph breaks followed by the url, then replacing that entire block of text with nothing. Also note we no longer need the “2” inside of our formula because that block of text does not appear again.
While it may seem much easier to simply do a find and replace in this instance, the substitute function will come in very useful if you have to clean hundreds of rows at once during a deeper analysis. Further, I do not know of a way to delete a paragraph break using CTRL+H, so you’d still be left with huge paragraph breaks at the end of each block of text.