Guilty admission: I find cool new tricks very exciting, especially when they provide a solution to an annoying or tedious task. So! You can imagine my excitement (and maybe even a happy dance or two) when I stumbled upon a gem in Twitter promising an easy fix to dealing with modified broad match keywords in Excel.
One click later, I found myself knee deep in Mark Jensen’s piece which offers a cool macro and step-by-step directions that will solve all your modified broad match problems. In fact, I was so intrigued that I had read the entire post before realizing that I didn’t need his solution. I found another way to deal with these pesky modified broad match errors years ago, and it’s much much much easier than geeking out with macros. I promise.
Problem: The error comes from the fact that Excel places a ‘=’ in front of all ‘+’ because it assumes that ‘+’ indicates a mathematical formula. So all we’re going to do here is a simple find and replace.
Solution: Replace all ‘=’ with a single (‘) apostrophe.
Result: Voila! By removing the ‘=’, the cell contents are transitioned from a formula to a general text format. You’ll also notice that the (‘) which is referred to as a leading apostrophe, will not be shown in the cell, so you’re left with your modified broad match keywords in perfect shape.
That’s it! Easy, painless, and should take you only a matter of seconds when you’re working with keywords in a spreadsheet. With that said, I’m actually a big fan of Mark’s solution, so if you have the time to go through the extra steps of working with his awesome modified broad match macro, check it out. I actually think it could prove to be extremely useful if you have a have a big workbook template that you work with often and don’t want to run F&R each time that you update the data.