Guest Blogger:  Laura Rodnitzky is the Director of Production for PPC Associates, a search engine marketing agency with offices in San Mateo and Chicago.

Writing PPC ads can be fun – using your brain to shoehorn great messaging into a limited number of characters is like doing puzzles. The parts of the copy-creation process that get really tedious are the repetitive acts – checking character count obsessively, combing the text to make sure your capitalization preferences are followed, appending the same word over and over.

There’s good news, though – Excel has some handy features you can use to streamline those mind-numbing steps. Here are my four favorite ad copy time-savers (they work for keywords, too):

1.       Length Formula

If you’re writing or customizing a significant number of text ads, create an extra column to the right of any ad text component to automatically measure length (i.e. the number of characters, including spaces, in the cell). Type in =LEN(cell), where cell is simply the cell whose characters you want to count. This feature makes it easy to see if you’re hitting the character count limits for Headline, Description Line 1, Description Line 2, or Display URL in AdWords. You can easily copy the formula down multiple rows and use conditional formatting (see #2) to quickly flag any cells over the limit.

2.       Conditional Formatting

It would be impossible to go into all the conditional formatting options in such a short blog post, so I’ll stick to the two main uses for ad text and keyword builds. First off, we can use the “Greater Than…” option to flag any ad text lines that exceed the specified limits. Go to “Conditional Formatting” > “Highlight Cells Rules” >  “Greater Than….” and enter your max limit in the dialog box that appears.

The screenshot below shows that the last two headlines exceed the 25-character max. As I modify the text of the headlines, both the length count and the conditional formatting will automatically update.

You can also use the “Less Than…” option if you want to identify ad text components that are significantly below the character limit. For example, you may want to flag any Description Line with fewer than 20 characters – perhaps there is additional, relevant text that can be added to use up more of the real estate and enhance the message you are trying to deliver.

Another useful conditional formatting tool is “Duplicate Values…” which is also found in the “Highlight Cells Rules” section. You can use the “Duplicate Values…” option to flag repeated keywords in keyword builds, duplicate ad text, etc. In the screenshot below, the two headlines flagged in green are duplicates.

3.       Capitalization Functions

Another useful feature for ad text is the “proper” function, which capitalizes the first letter of each word in a cell. Type in =proper(cell) to use this function.

There are a couple things to consider when using “proper.” First of all, the output appears in the cell where you place the formula, and the output itself is a formula. In order to manipulate it, you’ll need to copy and paste as special (values). You may want to paste it back into the cell with the original text, so you maintain the headers (“Description Line 1” in the example above). The other thing to keep in mind is that acronyms (such as PPC) will be modified so that only the first letter is capitalized. You’ll need to go back and fix any acronyms – fortunately a simple find/replace can do the trick.

There are additional capitalization functions that may be useful, depending on your preferences for keywords, ad group names, and ad text. The “lower” function makes every word lowercase, and the “upper” function makes all letters capitalized.

4.       Concatenate

The last feature I’ll hit on today is “concatenate,” which allows you to join the contents of two or more cells and/or cells plus text. For ads, “concatenate” is a great tool for appending tracking parameters to destination URLs, or for adding text to existing ads, among other things. For example, if you want to run an ad test with the word “Free” added to the beginning of every headline, the concatenate function would let you do this easily in Excel.

Note that, like the “proper” and “lower” functions, the output is placed in another cell and needs to be copied and pasted as values in order to manipulate the text.

Another great use for “concatenate” is to append the plus sign before broad match modifier keywords – however, it will only append the sign to the first token in a keyword. For the remaining tokens, simply use find/replace to find spaces and replace with space and plus sign.

There are several more functions and tools in Excel that will make your life as a search engine marketer much easier, but these are some of my favorites for ad text creation (and they can be handy for keyword builds, too).

Do you have any to add? Leave a comment!