Home > Uncategorized > XLS formula to remove Characters from the Keyword column

XLS formula to remove Characters from the Keyword column

One of the projects I am working on is to make a template to help standardize the Search Query Report (SQR) process for adding positive and negative keywords. I’ve been racking my brain for the last two days on how to automate some aspects of the SQR from Adwords so that we can more easily identify words to add as negatives.

I kept on getting stuck on something fairly simple: how to remove the match type identifiers from the keyword column. (ie “, +, [,]). I knew that i could use a substitute function to remove the identifiers, but as I embedded the functions together I kept on receiving an Error value.

So before i get started into the solutions, I want to start with what is the substitute function?

Excel Substitute Function

The excel substitute function is a simple function that allows you to identify a cell and find -something-, replace it with something else, and specify if you want it to happen a certain number of times or in every instance.
=substitute(text, old_text, new_text, [instance number])

So in my example, I want to use substitutes on a phrase match keyword to find the quotation marks surrounding the keyword and replace them with “” (NOTHING.) For broad modified keywords, i.e. +keyword, i want to find the + symbol and again replace it with “” nothing. For exact match keywords i want to identify the brackets [] and again replace them with “” nothing. This will give me just the keyword.

The next step is how to use the substitute function to do this, and where I went wrong…

Remove PPC Keyword Match Type Identifiers from the Keyword Column using Embedded Substitute Functions

A really really smart co-worker, Sam James, came up with the following solution that involves embedded substitutes with right and left trim functions. This works and is 100% functional and it removes all of the match type identifiers from the keyword column.

In this example the KEYOWORD column is B. So we’re going to be referencing cell B2 because it is the first row in column B with a keyword.

=IF(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,””””,””),”[“,””),”]”,””),”+”,””),1)=” “,RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,””””,””),”[“,””),”]”,””),”+”,””),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,””””,””),”[“,””),”]”,””),”+”,””))-1),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,””””,””),”[“,””),”]”,””),”+”,””))

As I mentioned this is 100% accurate. However, my brain has a really hard time processing the logic because of its complexity. I knew that there had to be an easier more straight forward method. I also decided that instead of hardcoding in the characters like quotation mark, i.e. “”””” four quotes in a row identifies a single text quotation mark, “[” identifies the left bracket in text, i would use the ASCII character identifier so I would be less likely of screwing up the substitutes.

My original embedded substitute was failing because I kept on leaving a additional comma after the new_text. i.e. =SUBSTITUTE(SUBSTITUTE(B2,CHAR(34),””,),CHAR(91),””,)). My logic was that I wanted to substitute for all instances, however it was expecting a value after the comma so it caused my formula to give me the dreaded #VALUE symbol.

After pulling my hair, screaming bloody murder and wanting to cry. I used Sam’s logic above and removed the extra comma.

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,CHAR(34),””),CHAR(91),””),CHAR(93),””),CHAR(43),””))

Trim removes extra spaces including a proceeding space incase your first word in the keyword phrase was broad modified.
This works! Wohoo. Now on to creating a formula to idenifty when a search query term matches exactly to a exact match term so I know where to add NEGATIVES. 🙂

When I’m done with all of this goodness it’ll be posted to the PointIt.com blog.

Happy Friday!

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: