{"id":239,"date":"2010-04-26T21:13:26","date_gmt":"2010-04-27T04:13:26","guid":{"rendered":"http:\/\/www.funk.co.nz\/blog\/?p=239"},"modified":"2010-04-26T21:13:26","modified_gmt":"2010-04-27T04:13:26","slug":"using-formulas-for-find-replace-in-excel","status":"publish","type":"post","link":"https:\/\/www.funk.co.nz\/blog\/online-marketing\/using-formulas-for-find-replace-in-excel","title":{"rendered":"Using Formulas For Find & Replace in Excel"},"content":{"rendered":"

Now this is what I call a spreadsheet!<\/p>\n

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<\/a>, one of my companies many motorhome hire websites.<\/p>\n

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.<\/p>\n

Those tips are underneath, here is the screenshot:<\/p>\n

\"Trace<\/a>

Trace Dependants in action<\/p><\/div>\n

Adgroup names<\/strong>
\nThese have been made using the following function: =CONCATENATE(D$1, \" - \", B8106)
\nWhat 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.<\/p>\n

Keywords<\/strong>
\nInstead 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.<\/p>\n

Ad Creative - Titles and Descriptions<\/strong>
\nAgain the substitute function is deployed: =SUBSTITUTE(S7, $G$1, $D$1)
\nSame story here, we are taking a line from the existing creative, search and replacing it so a creative like this:<\/p>\n

Motorhome Hire Barcelona<\/a><\/div>\n
Hunt Down The Best Barcelona Camper<\/div>\n
Rental Deals. Book Online and Save<\/div>\n
FetchRVRental-Spain.com\/Barcelona<\/div>\n

is automatically turned into this:<\/p>\n

Motorhome Hire Bilbao<\/strong><\/a><\/div>\n
Hunt Down The Best Bilbao <\/strong>Camper<\/div>\n
Rental Deals. Book Online and Save<\/div>\n
FetchRVRental-Spain.com\/Bilbao<\/strong><\/div>\n

This works pretty well but can cause problems if you reach the 25 or 35 character limits.<\/p>\n

Ad Creative - Display URL and Destination URL
\n<\/strong>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.
\nDisplay URL: =SUBSTITUTE(CONCATENATE(Y7, \"\/\", $D$1), \" \", \"_\")
\nDestination URL: = SUBSTITUTE(SUBSTITUTE(AA7, $G$2, $D$2), \" \", \"_\")<\/p>\n

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.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,17],"tags":[30],"class_list":["post-239","post","type-post","status-publish","format-standard","hentry","category-google-adwords","category-online-marketing","tag-excel"],"_links":{"self":[{"href":"https:\/\/www.funk.co.nz\/blog\/wp-json\/wp\/v2\/posts\/239","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.funk.co.nz\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.funk.co.nz\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.funk.co.nz\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.funk.co.nz\/blog\/wp-json\/wp\/v2\/comments?post=239"}],"version-history":[{"count":0,"href":"https:\/\/www.funk.co.nz\/blog\/wp-json\/wp\/v2\/posts\/239\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.funk.co.nz\/blog\/wp-json\/wp\/v2\/media?parent=239"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.funk.co.nz\/blog\/wp-json\/wp\/v2\/categories?post=239"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.funk.co.nz\/blog\/wp-json\/wp\/v2\/tags?post=239"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}