Using Formulas For Find & Replace in Excel

Now this is what I call a spreadsheet!

Here you can see an 8,000 row spreadsheet in Excel 2007 which is being used to prepare a bulk upload into Google Adwords editor, for use in the Adwords campaign for Fetch RV Rental Spain, one of my companies many motorhome hire websites.

It's here mostly because of the cool "trace dependants" arrows in blue, many they look trippy. But the other reason I am posting this, is to provide some help and guidance for other search marketers out there on some tips and tricks for doing really large campaign rollouts in AW editor.

Those tips are underneath, here is the screenshot:

Trace Dependants in action

Adgroup names
These have been made using the following function: =CONCATENATE(D$1, " - ", B8106)
What this does, is generate brand new adgroup names according to what's in constant cell D$1 ("Bilbao") and attached onto the end of this whatever is in the B column which shuffles through the various campaign geotargetting settings. You can see "UK/IE Targets" here, but further down this changes to "EU Target" later on down the sheet.

Keywords
Instead of using the GUI part of Excel like a normal person (you know - Find and replace), I've used the dynamic version of the same: The substitute function. This one looks like this: =SUBSTITUTE(K13, $G$2, $D$2). So it takes cell k13 ("barcelona camper hire"), then looks for G2 ("barcelona") and swaps it for D2 ("bilbao"). Very tidy.

Ad Creative - Titles and Descriptions
Again the substitute function is deployed: =SUBSTITUTE(S7, $G$1, $D$1)
Same story here, we are taking a line from the existing creative, search and replacing it so a creative like this:

is automatically turned into this:

This works pretty well but can cause problems if you reach the 25 or 35 character limits.

Ad Creative - Display URL and Destination URL
These work in the same way as the other replacements, but I have nested a further layer of substitutes, to remove the space and convert that into an underscore.
Display URL: =SUBSTITUTE(CONCATENATE(Y7, "/", $D$1), " ", "_")
Destination URL: = SUBSTITUTE(SUBSTITUTE(AA7, $G$2, $D$2), " ", "_")

The Display URL uses a concatenate to simple join the placename to the end, but also to put a / slash in between. The destination url uses two substitutes, one to swap out the keyword for one which could have a space in it, and then another set to replace the space with an underscore.