The Concatenate Function – A Gift from Excel for PPC Marketers

The term concatenate means “to combine” or “to join together”. This a function that is used in Excel to combine multiple cells together into one cell. It can also be used to add further words or symbols to multiple cells at once.

For PPC marketers, the concatenate function is something that can really speed up day-to-day work. Below we shall explore some examples of using concatenate and how it benefits certain tasks.

Using concatenate when building advert campaigns

One function for concatenate in PPC is during campaign building, more specifically, when adding the broad match modifier keyword type. The issue we have is adding the “+” symbol to the beginning of the keyword.

With the keyword list shown, we use the Find & Replace function to add the “+” symbol between the words.

However a “+” symbol needs to be added to the beginning of the keyword to form a broad match modified keyword. This is where the concatenate function comes into play. In an adjacent cell we insert the concatenate function:  =CONCATENATE(“+”, B2).

When we are using the concatenate function to add new words, symbols or even a space to existing text in Excel, whatever is being added must be contained within speech marks in order for Excel to recognise what you are trying to add. In the example shown, we add the function exactly as is shown and we can then use the fill handle to copy the formula for the cells below B2.

In the above example, it wouldn’t be too time intensive to go through the list manually and achieve the same outcome. However when you apply this to a sheet with hundreds or even thousands of keywords, this simple function can save you significant amounts of time.

Using concatenate when carrying out negative keyword reviews

When carrying out negative keyword reviews, the concatenate function is the perfect formula for adding multiple keywords to your negative keyword list on AdWords. When downloading historical search queries, you are automatically given the list in Broad, but you may want to re-upload them on Exact or Phrase for example.

The task of editing each cell to have [Exact Match] brackets on either side for example can seem intimidating at first glance. Luckily, concatenate does all the hard work for you. In this example, we use columns B and C in the Excel spreadsheet and enter a “[“ in cell B2 and a “]” in cell C2. We then use column D for the concatenate function to complete the process. The concatenate formula that is entered into cell D2 will be: =CONCATENATE(B2, A2, C2). Enter the text cell (A1) in between the two “[“ and “]” cells to ensure the exact keyword match type is done correctly.

We can then use the fill handle again to drag columns B, C and D down to row 11 and transform all the keywords into the exact keyword match type. This leaves us with a negative keyword list that is ready for upload into AdWords.

Using concatenate to build out a keyword list

When building a new PPC campaign, you often have a list of keyword variations that you’d like to include, but writing each one out can take time. For example, let’s say we want to cross reference the types of footwear with the people wearing them:

Types	People
Shoes	Womens
Boots	Mens
	Childrens

The best way to get each variation quickly is to use concatenate. Using the process outlined previously, and using the following layout, we can quickly get a list of keywords by copying the formula down and across:

As you can see, the formula breaks because there are cell references that automatically move as you copy them. In order to avoid this, you simply add a $ symbol before the elements you want to remain consistent. In this instance, we want the cell to always reference column B for the People, and Row 1 for the Types, but everything else can be automatically moved as you copy down and across: