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.
Two Approaches
Excel gives you two ways to concatenate:
CONCATENATE Function
Syntax 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.
Real-World Examples
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
poachresearch. - 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 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.
Learn More
For more beastly data visualization tips, check out out my Annielytics Dashboard Course offerings.
i found a new use! I found a new use! New for me anyway. 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.
That’s a perfect use! And a great example of where automation totally works. Adding it now.
I never knew if we could use ampersand in concatenation. I hate this concentrate in excel as there’ll be unreadable commas. Thanks for the eye opener Annie.
(glad it wasn’t only me!)
Happy to help, Karthik!
Thanks Annie, this is great. I’ve just used Join in Google docs to great effect! Did not know it existed and turned a 5 minute job into a 10 second job!!
Combining over 100 different parameters into one comma separated string so I can exclude them from a Google Analytics profile is a pain in excel but so easy with the join function. 🙂
Yay! Glad I could make your job a little easier, lady! I’m pulling together all of my research for a lengthy appeal to Microsoft for adds. Adding JOIN and SPLIT functions will be in that post.
Great post and despite being an avid user of concatenate – I never knew you could use an ampersand!! I have been using it for vlookups on two fields quite frequently, particularly for pulling in campaign data and other metrics…
I 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 –
Ohh I never thought to stitch together HTML with concatenate. That’s a great idea! I always have to double check my HTML on the rare occasion I have to write it.
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.
Thanks for your input, Jason! I can’t really picture it because I can’t picture the steps. But I’m sure it resonated with other PPC marketers.
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.
Oh yeah. That makes total sense. Thanks.
Annie,
I’m a total dufus in Excel, but your work inspires (scares the hell out of) me. And makes me say “wow” nonstop.
RS
You are always too kind, Ronell! <3
Great examples. I use concatenate all the time, most recently for the reconsideration process. When downloading URLs from multiple sources it makes it easy to (after stripping http/s://www.) to add a single http:// so that you can easily de-dupe your list.
Likewise, you can use text to columns to break down a raw list of urls into root, domains, you can then use concatenate to keep the first url for each domain and get rid of the rise.
I’ve also used it in conjuction with scrape this to concatenate first and last names.
It’s a pretty brilliant function!
Hey Marc, great ideas. I didn’t really follow the second one, but for the first one, I demonstrate how to do that w/o having to use Text to Columns. https://annielytics.com/blog/excel-tips/how-to-pull-domains-out-of-a-list-of-urls-in-excel-video/
Haha sorry for the horrendous grammar – speed typing on mobile:$ That search method is pretty sweet and almost insultingly simple… good to know!
The second use was for pairing sample links with their root domain within a list of raw URLs. Relevant because most referring domains link from multiple pages and this provides a clutter free way to see what a link from a given domain looks like.
Text to columns > (where I would previously use find and replace, I’ll now use your search method to) > dedupe domains> you’re left with excess paths that you can trim to line up with remaining roots. in the next column you use concatenate to bring together the path with the root domain. Just make sure to use a wide cell range to account for links from deep pages.
Copy and paste values and you’re left with the root domain in one column and a sample link from that domain In the next.
The whole process takes about 30 seconds
Ah. I gotcha. Thanks for clarifying!
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.
Brilliant, Katie. Added this to the list and updated the post title.
Hi Annie, any tips on how to skip over an empty cell? I can’t find anything on the web that works for me. Do I use IF?
Hi Liz, can you explain your bigger objective? What are you doing that you want to skip blank cells?
I am putting together a campaign tagging map and in some instances, there isn’t anything for utm_content or utm_term. I want to put together a string that will skip over the cell if it is blank, but add if there is something in the cell. I hope this makes sense. Thank you for your time!
Oh sure! Here’s the formula I use: =IF(B9″”,CONCATENATE(B9, IF(ISNUMBER(SEARCH(“?”, B9)), “&”, “?”),IF(C9″”,”utm_medium=”&C9,””), IF(D9″”,”&utm_source=”&D9,””), IF(E9″”,”&utm_campaign=”&E9,””), IF(F9″”, “&utm_content=”&F9,””), IF(G9″”,”&utm_term=”&G9,””)),””). I created a Google Spreadsheet you can copy and use, if you want: http://bit.ly/OgdA0a. I just wrote a guide on campaign tagging. You can check it out here: http://bit.ly/ga-campaign-tagging.
Thank you!
You’re welcome!
All I have to say is DAYUM! lol. Excel wizardry 🙂
Hello Anni,
Thanks for the wonderful tutorial.
Is there any way to concatenate the data from web and then import it within only one cell. In detail – there is only one line in a url , wither yes or no. but when the page is imported to excel , it insert 1 or 2 extra cells as there are blank lines in the url , so by adding the 1st line 2nd line and 3rd line i can import the data to one cell to avoid excel creating new cells.
Appreciate your patience.
Thanks,
Amar
I often work in Excel VBA and often have long, lengthy code that is repetitive except for cell references or one or two variables. I often write the code (or copy from VBE into a worksheet, which luckily breaks each line into separate rows). Then I use a combo of search/replace to add delimiters and Excel’s Text-to-Columns feature to break each row into segments. I keep the portions that will repeat in their own respective cells and then populate a variables column (or several) with the respective data. In a blank column, I then concatenate the first row, using the ampersand (absolute referencing the repetitive data and relative referencing the variables) and drag the concatenated column down to match the number of rows with variables. Each row will contain the entire line of code which can then be copied and pasted back into the VBE, error-free. It’s a real time saver and (as someone who gets very impatient with tedious repetitive tasks) stress reducer.
Impressive use of concatenation!
Hello Anni,
Thanks for the wonderful tutorial.
Is there any way to concatenate the data from web and then import it within only one cell. In detail – there is only one line in a url , wither yes or no. but when the page is imported to excel , it insert 1 or 2 extra cells as there are blank lines in the url , so by adding the 1st line 2nd line and 3rd line i can import the data to one cell to avoid excel creating new cells.
Appreciate your patience.
Thanks,
Amar
You would need to do this post-import with a macro.