4 Excel Formulas for PPC Keywords That You Should Know

Fun Fact: I’ve been using Excel since I was 13, organizing my life, life goals, budget and my once-upon-a-time engineering work into spreadsheets on top of spreadsheets on top of spreadsheets (I’m now 28). And a lot of these functions, formulas and pivot tables can be seen permanently engraved on the inside of my forehead. So when I entered the world of SEO, SEM, and PPC, my intense appreciation for Excel was a useful skill set to leverage.

If you’re using Excel to draft a keyword list for your PPC search campaign, four useful functions you should be using include TRIM, CONCATENATE, SUBSTITUTE and LOWER. These four functions can make your keyword drafting process faster and more productive.

TRIM, CONCATENATE, SUBSTITUTE and LOWER

1) The TRIM function removes all extra spaces in a cell, leaving only one space character between words and zero space characters at the beginning or end of the string.

=TRIM(text)

2) The CONCATENATE function combines two or more text strings into one string. An alternative to the CONCATENATE function is using the AMPERSAND character instead.

=CONCATENATE(text1, [text2], ...)
=A1&"text1"&B2

3) The SUBSTITUTE function replaces a specified text in a string with new text. The function replaces all occurrences of the specified text by default but you can also specify the number of replacements using [instance_num].

=SUBSTITUTE(text, old_text, new_text, [instance_num])

4) The LOWER function converts all characters in a string to lowercase text.

=LOWER(text)

Some examples

How To Update Broad Match Type to Broad Match Modifier Keywords?

If you need to update your broad match type keywords into broad match modifier keywords, leverage the SUBSTITUTE function and AMPERSAND character. You can replace all space characters with “ +” and insert a “+” at the beginning of the string.

="+"&substitute(A1," "," +")

How To Update Broad Match Type to Phrase or Exact Match Keywords?

You can use either the CONCATENATE function or AMPERSAND character. Personally, I prefer the AMPERSAND as I find it quicker to type. Note that Excel uses quotation marks for formulas, so you need to define the “ character by typing it four times.

=""""&A1&""""
="["&A1&"]"

With the CONCATENATE function, you insert the quotation marks or opening/closing brackets between the text.

=concatenate("""",A1,"""")
=concatenate("[",A1"]")

How To Clean Up Your PPC Keyword List?

Keywords in Adwords are not case-sensitive, so to keep things clean and organized, keep your list lowercased. When analyzing search queries from previous campaigns, users may often have spelling mistakes and extra spaces. Use the TRIM and LOWER function along with the “Spelling” tool to correct misspelled words, turn them lowercased and remove all extra space characters.

Please share this blog post if you found it useful!

Neil Laborce.