Excel Formulas for PPC Keyword Match Type Manipulation

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.

Keyword Conversion
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.

Exact Match

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.

Phrase Match

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.

bmm

 

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.

4 Responses to “Excel Formulas for PPC Keyword Match Type Manipulation”

  1. Patrick Hathaway July 11, 2013 at 6:49 pm #

    Hey George – great post! And thank you for TRIM! I haven’t used that before but can see its immediate benefits.

    When I was doing PPC I built myself little workbooks like this so I could just plug the keywords in and it would do the rest. What would be amazing is if you could add a downloadable workbook to this post (or somewhere else) that handles all this stuff for you…

    Thanks again – love a bit of Excel in the evening.

    • George Gilmer July 11, 2013 at 7:00 pm #

      Hey Patrick,

      Thanks for the comment and suggestion! It’s always great to hear from a fellow Excel geek.

      I tend to whip up formulas on the fly based on need, but can see how a downloadable workbook would be helpful and a great time saver. I’ll begin putting something together.

  2. Prasad Ajinkya July 12, 2013 at 10:59 am #

    This is a great post! Could you keep writing more about how Excel is used for operational tasks in PPC management.

    Did you know that Bing (I know Not Google) had released a nice Excel plug-in out there which helps you do keyword research and more?

  3. Jason Manion July 26, 2013 at 8:24 pm #

    Great post! I have been trying to learn more Excel recently to help with the PPC stuff I do. I recently started using TRIM and CONCATENATE (I’ll have to try the short version) and they can definitely save a ton of time.

Leave a Reply