Hey! Welcome to another edition of #FunctionFriday! One of Excel’s Swiss army knives for marketers is the ability to concatenate things and stuff. Oh, and if you’re new to marketing geek speak, concatenate just means to stitch together. We just make words multi-syllabic so we can charge more for our services.
Excel gives you two ways to concatenate:
CONCATENATE(text1, [text2], ...) Disclaimer: I really don’t like the CONCATENATE function. Why? Well, because it’s a super lame substitute for what marketers really need, which is a JOIN function. You know … the one like Google Spreadsheets have, even though they’re a far-inferior data analysis tool. The JOIN function allows you to pick a range of cells and choose a delimiter. So if you needed to build out some regular expressions (aka regex) with pipe characters (the character above the backslash that means “or” in regex), you could just select the range you want to join and tell it to deliminate with a | by hard coding it in the formula bundled up in quotes or referencing it in a cell (my pref). So awesome. But noooo. We have to play Pick Up Sticks with our data and manually concatenate strings. So this is far from a power tool. But it’s what we have to work with. It can be a huge pain to choose each cell, enter a comma, rinse and repeat. To dial back the pain level a bit, let me share a trick I learned from Bill Jelen (aka MrExcel): Choose multiple cells by holding down the Ctrl key (Mac: Command key) while you’re choosing the cells you want to concatenate. This will automatically enter commas in between arguments. Still lame. But definitely not as painful as manually entering (post spoiler alert …) ampersands or commas ad nauseam. So the only time I use the CONCATENATE function is if I have to reference more than three cells.
Concatenation Operator ( & )
When I first started using ampersands to concatenate data in Excel, they reminded me of Tinker Toys. For you kids under 35 or lacked a cool grandma, see feature image. They were cool and provided hours of entertainment (and sibling rivalry). Getting back to my analogy, the ampersand (&) is a lot like the connectors that are the glue that hold the rest of the structure together. Like I said, this is usually my concatenate tool of choice. The way it works is wherever you want to join different elements together you just throw in an ampersand. As with many functions in Excel, you can either reference cells, named ranges, or hard coded strings (wrapped in quotation marks). I almost always reference cells. It looks something like this: If you want a space, comma, symbol, or anything else added to your text, you need to add those in between the quotes.
I took my quest for concatenation examples to the street and asked marketers for examples of how they use concatenation in Excel and added them to my own examples below.
- Join source + referral path from Google Analytics to get full URLs of linking sites sending traffic. (See my Search Engine Land post on this for directions and a custom Google Anaytics report.)
- Join hostname + request URI from Google Analytics to stitch together the subdomain and URI in content reports. This is necessary if your site has subdomains but you’re not using a hostname filter. See post mentioned above bullet for directions and [another] custom report. Geez, they’re giving out custom reports like pot in Washington over there!
- Add “http://” to a list of URLs to make them clickable or enter them into Screaming Frog for further analysis. This is easy. Just put the http:// in one cell and concatenate. Just remember to lock down your reference to this cell (e.g., $A$4).
- Join different elements together for a chart title. There’s a lot going on in the screenshot below, but all I’ve done is piece together a dynamic chart title in a cell using &s and then referenced that cell in the title. To have your chart title reference a cell, select the title and enter the reference to the cell that contains the concatenated text in the Formula Bar. You can’t use the concatenate operator in the formula bar. You need to do it in a cell and then reference that cell. Obviously, I wouldn’t do all of this on the main page of a dashboard (like the ones I teach people to create). I tuck away calculations on a Calculated Data worksheet. This is for illustrative purposes only.
- Create tracking parameters for marketing campaigns. (Or you could use my dynamic Google Spreadsheet and call it a day.)
- Create redirects for your .htaccess file (or equivalent). All. Day.
- I remember last year Meg Geddes (aka Netmeg) recounting using &s to create domain names to
- Piecemeal together mea culpas (aka disavow files) to assuage the wrath of the Google gods. (Hat tip to Marie Haynes for reminding me about this one here!)
- Build API queries.
- Add text to a number format. (See my Search Engine Land post on this and search the page for “adding text to number formatting.”)
- Advanced: Do a vlookup using more than one criteria. This is a total hack that I learned from Mynda Treacy’s amazing dashboard course. I can’t find it published anywhere. The closest I’ve found is this help thread. Not as elegant as Mynda’s but will get the job done. It’s worth noting that you can concatenate the lookup_value, table_array, or col_index_num.
- Michael Cropper had another unique approach to incorporating CONCATENATE into a vlookup. You can check it out here.
- Brett Snyder came up with an innovative way to write Googlebase descriptions using CONCATENATE and sentence templates. Very sleek. Just don’t get crazy and spammy. (You know who you are!)
- The storecoach.com site had a tutorial on how to build feed files using CONCATENATE.
- The White Shark Media site had a tutorial on how to write AdWords campaigns using CONCATENATE. (I will caution against automating this process too much. Your audience isn’t bots.)
- Several marketers mentioned using concatenation operators to build keyword lists. My opinion on this one: I think tools like mergewords, Ontolo’s free keyword generator, and others (search the page for “keywords”) are far more efficient.
- Jason Losover said he uses them to build meta titles here. Be careful with that. I’m a bigger fan of having an actual homo sapien who exchanges carbon dioxide and oxygen to write custom titles to keep the death-by-automation levels of your site under control.
Pro Tip: After concatenating, before you can use this tinker toy data, you need to copy and paste as values. I usually do this in the next column over to my formula live in one column.
Am I Missing Any?
Let me know if I’m missing any in the comments below. I’m sure PPC and affiliate use cases are way under-represented in this post.
- Meg Geddes came through with another great use. She added this: Constructing bulk social media posts. See, Hootsuite lets you upload them via CSV file for scheduled posting to Twitter, Facebook and Google+. So I built a little “builder” file in Excel using concatenate, and I can whip out several months worth of social media posts (formatted exactly for Hootsuite scheduling) for nine sites in about an hour. Maybe longer during the summer. And before you say it, these are event sites, so it makes perfect sense to automate, and I automate the hell out of them. [Editor's Note: I totally agree with leveraging automation for a purpose like this! So there, Meg.]
- Gerry White uses CONCATENATE to build HTML. Never thought to do that! Here’s what he said in his comments: Have often used concatenate to write HTML that i can then cut and paste out of it – this is great for when I want something like “top sellers” works really well with the concatenate and other resources such as Neils Bosma. Editor’s Note: Here’s a link to the amazing (and free!) SeoTools add-in for Excel, a must-have for all marketers.
- Jason Manion represented on the PPC side with this addition: And for you PPC people out there, you can also use concatenate for specifying match types on keyword lists. Once you get past a few keywords, using concatenate to add your quotation marks or brackets or plus signs is much quicker than manually adding them.” He clarified further in this comment: “If you have a list of keywords that you need to turn into phrase match or exact match or modified broad match, then you can concatenate the punctuation to the list of keywords easily. For example, =concatenate(“[",b3,"]“) would turn your keyword b3 into an exact match keyword.”
- Katie Walton added this in a comment: “I use concatenate to semi-automate reports. Use the EOMONTH formula to set the date and add text… e.g. automatically say “[this month] compared to [last month]” as a heading. Import the stats using Excellent Analytics, then link it all to powerpoint so it looks pretty.” Editor’s Note: Positively brilliant. Once you have that in a cell, you can also link a chart title box to it, and you’ll also have a dynamic chart title.