.png)
TL;DR: "Concatenate" and "&" formulas perform basically the same tasks, but with slightly different formatting. You can combine text in one cell while adding in additional data wherever you choose. TextJoin is best for combining data in a very consistent pattern, such as always separated by commas or hyphens. It's a cleaner looking formula, but doesn't allow variation.
Maybe it's the pandemic, but lately analogies have taken a turn for me. For example, while I previously would have used a comparison about people coming together, I now feel like a cocktail recipe makes more sense. Just me? That's okay. Let's talk metaphorical cocktails! Specifically, let's talk about the formulas Concatenate, its lesser-known doppelgänger "&", and TextJoin. Concatenate is that standard, get-the-job-done Excel cocktail. It's known and loved by many, doesn't judge when you want to get a little crazy with the data choices, and and has a simple format that's easy to remember. We'll call it the Long Island Tea. But sometimes you want something else. Sometimes typing out "c-o-n-c-a-t-e-n-a-t-e" is frustratingly slow, and you just want it done already! So what's easier and faster? The simple ampersand. No long word to remember, not even parentheses! Just a simple character between the things you want to join. That's our Martini. Finally, sometimes you want to glitz it up. Sometimes, you want something that takes precision, makes an appearance, leaves an impression. That's when we go with the Manhattan. Or in Excel, TextJoin. Let's break these down so you see what I mean.
Concatenate
Concatenate in its most basic form looks like this:
=concatenate(A1,B1)
Concatenate means to link things together in a chain. Whatever you list in those parentheses will be listed one after the other, exactly as they appear in their cells. Let's pretend A1 says Long and B1 says Island. The above formula would return "LongIsland". No space, no punctuation. To get those things, you'll need to add some info. So let's try again.
=concatenate(A1," ", B1," Tea!")
So now, we're stringing toghether "Long", and then a space, and then "Island", and then a space and the word "Tea!". Say it with me: Long Island Tea! Anything you list within quotation marks (which are within commas) will appear exactly as listed. Whatever order you list the the references is the order they will appear. You can link together 3 things or 53 things - it's a pretty forgiving formula. However, it gets long and ugly fast. Also, if you're not careful, there could be no rhyme or reason to your data choices, which means coming back a month later or assigning a spreadsheet to someone else to maintain becomes a huge headache when someone needs to make a change. What other options do we have?
&
The name so short that it looks weird all by itself. Glance back up at that concatenate formula. Now check this out:
=A1&B1
How easy is that?! And it's going to return the same thing as that first concatenate formula! What about the second formula?
=A1&" "&B1&" Tea!"
See? The contents of A1, and a space, and B1, and a space with the word "Tea!" at the end. Long Island Tea in half the space! It has the same flexibility of Concatenate, just with less formatting. If you put the data in quotes, it will appear exactly as you type it. If you list a cell reference, it will list the data that is in that cell. The other cool part, you can say it outloud to help you get the formatting right: "I want A1 AND a space AND B1 AND a space..." short, simple, martini. However, it has the same downside - it can get ugly and messy. Since "&" is such a flexible formula, you can string any mix of data, making it hard for someone to come behind you and figure out what's going on.
So how do we get this organized? How do we really flex that Excel skill and show not only neat, organized formulas, but data that can be understood by anyone, even a year from now? Our fancy Manhattan, TextJoin.
TextJoin
Let's see this beautiful beast in action:
=textjoin(" ",false,A1,B1)
That's it. There aren't 15 ampersands. We could make it huge, and it would still look exquisite!
=textjoin(" ",false, A1:G1)
See how clean that looks? And what would that return? It would return each of the cells in the range listed (A1 through G1) with a space in between. That is how TextJoin cleans up your formula. You enter one separator, then you decide if you want to skip blank cells (false means you do not) then you list what cells you're stringing together. They can be individual cell references or in a range - either way works. That's it. You can't get crazy with the data and decide to add random phrases in the middle of your formula. You can't switch from commas to hyphens to colons. You keep it uniform, easy to read, easy to understand.
The clean simplicity of TextJoin is the little hint of bitters in your Excel Manhattan – that certain extra something you never knew you needed, but once added it makes eeeverything better. How TextJoin Super-shines
In summary, concatenate was the gold standard, then ‘&’ made it easier, and now TextJoin cleans it all up. TextJoin super-shines if you have to type a formula like this every day, because you don’t have to type the separator every time, you just type it once. (Instead of “Join first name AND a space AND last name AND a space AND title AND a space”, it just becomes “use a space as a separator to join First, Last, Title”.)
Booziness aside, if you want clean, easy-to-read formulas that you'll be able to decipher a year from now, TextJoin saves you a lot of the headaches and wheel reinvention that Concatenate and "&" let you fall into. For my fellow Energy analysts, check out the following image to see how a VERY common formula string we make every day can be cleaned up:
Helpful? Want to see a specific example of something Excel- or lease-related? As always, drop me a line or leave a comment!
Comments