I’m a firm believer in the power of one’s own resourcefulness. This is especially true in the word of online marketing. SEM managers have a natural tendency to love any tool that can help get the job done quickly and effectively. What’s even better is having the ability to roll your own tools when necessary. It’s probably no surprise that the tool I usually consider first is Microsoft Excel.
A few of simple Excel formulas can make your life easier when dealing with both positive and negative keyword match types. The beautiful thing is that pretty much any keyword match type manipulation can be performed using the following three formulas.
The Function Trifecta!
TRIM
This is one of the most useful functions and one of my all-time favorites. I can’t begin to explain how much time that this saves over the course of a workweek. If you’ve ever performed keyword research and left trailing or double spaces in cells, then this function will solve all problems. The TRIM function simplly removes leading and trailing spaces as well as any consecutive spaces that may appear within text.
=TRIM(A1)
CONCATENATE
I’m personally not a huge fan of the long form version of the CONCATENATE function and rarely, if ever, use it. I’ve found that the abbreviated ampersand form is a much better fit for my workflow. Both the CONCATENATE function and ampersand combine values either inputted manually or from different cells into one cell. It’s important to note that you must manually insert a space between values. This can be accomplished by inserting a space within quotes.
=CONCATENATE(A1,”text”,C1)
=A1&”text”&C1
SUBSTITUTE
Find and replace is ok, but those who really want speed up their day use the SUBSTITUTE function to swap values. The SUBSTITUTE function takes a value that you define and replaces it with another value that you define. The true beauty is that you can nest multiple SUBSTITUTE functions to perform many swaps simultaneously or in preferred orders.
=SUBSTITUTE(A1,”original value“,”replacement value“)
Easy Match Type Manipulation
Let’s break down how to quickly convert any keyword into any match type. We won’t explain how each match type behaves, but will illustrate how to quickly achieve any match type change that is needed.
Always use the TRIM function:
First, always wrap keyword text in a TRIM function. If the TRIM function is not used there is a risk of adding a trailing spaces or nonsense values into keyword lists. This can be an especially annoying problem when adding hundreds or thousands of keywords into an account.
For those wondering how to view formulas within multiple cells, try playing around with CTRL + ~. It’s a bunch of fun and quickly reveals what’s taking place in complicated or unfamiliar spreadsheets.
Exact Match
Creating an exact match version is perhaps the simplest conversion to accomplish. Simply CONCATENATE each side of the cell with opening and closing brackets. Both the standard usage of CONCATENATE and the ampersand form are shown below.
Phrase Match
The trick with creating a phrase match version of a keyword is getting the quotations to correctly display. Since Excel by default uses quotes to indicate text, four consecutive quotes must be used in order to display correctly.
Broad Match Modifier
This is one of my favorites. Truth be told, I get excited anytime that I have the chance to use SUBSTITUTE. In this case, use CONCATENATE to prepend a plus sign then use SUBSTITUTE to replace a space with a space and plus sign.
Save Time, Reduce Errors, and Save More Time.
Time is of the essence in PPC, especially when managing large accounts or multiple accounts. Incorporating these simple formulas and functions into your workflow will save time and greatly reduce the chance of errors, which ultimately saves more time.
