Excel functions exist to make things easier. Concatenate is one such text function primarily about displaying multiple cell values in one. However, Concatenate is slowly going obsolete and has been replaced by Concat since Excel 2016. Still, it works fine, and there is no difference a user can spot for now. But still, there are some advantages of Concat over the outdated Concatenate that we will see at last. Besides, we will check out Textjoin as an alternative to both. So, let’s break this down to see how it works.
Concatenate in Excel
You can apply concatenating similar to using any other formula. Press = on the output cell and type Concatenate. Afterward, include the cells in the parenthesis with the proper syntax and press enter to get the result. For instance, one will use =Concatenate (A1, B1) to combine cell A1 (Micro) with the B1 (soft) to make Microsoft. Notably, one can’t select the subject cells by dragging the mouse over them because this will return =Concatenate (A1: B1), producing two columns, and won’t give the desired result in one single cell. Alternatively, you can use ‘&’, a calculation operator, to have the same result, =A1&B1. This was the simplest case without any spaces or separators. Ergo, let’s check out some variations.
Concatenate with commas, spaces, hyphens, etc.
This is a more practical one where you combine multiple string values using commas, spaces, hyphens, or random text. So, I had three string values: Geekflare (A1), is (B1), and good (C1). And what worked is =Concatenate(A1," “,B1,” “,C1). Again, this can also be done using the “&” character. However, the formula becomes a bit unfriendly: =A1&” “&B1&” “&C1. The space between the words was input using the double quotation marks ” “. Similarly, one can use comma (“,”), hyphen (“-“), a text (ex. “abc”) or number (ex. “12”). For instance, you can use a text argument as shown in the given image: Here, I added ‘Welcome’ along with string values ‘to’ and ‘Geekflare’. Please note, I have added space with the text itself: =Concatenate(“Welcome “,A1,” “,B1). Alternatively, =Concatenate(“Welcome”,” “,A1,” “,B1) would work the same. The next sections are about some differences in similar functions.
CONCAT vs CONCATENATE
Importantly, Concat is compatible with ranges (A1:B1) while Concatenate isn’t. However, using the range argument will give you a result without any spaces, limiting its real-life use cases. Besides, this is easier to remember, type and is the updated version we should follow.
TEXTJOIN vs CONCATENATE
Textjoin is a more advanced function for concatenating. You can use this to combine ranges, ignore empty cells, and include delimiters, making it ideal for data-heavy excel sheets. The function used in the above image was =TEXTJOIN(” “, TRUE, D1:J1). The first part, the delimiter, is the object injected after each value. Here we used a space (” “), something we couldn’t do with Concat or Concatenate. Similarly, one can use commas, hyphens, or anything else. Next, the argument for ignoring empty cells, True. If for False, we would’ve got an extra space for cell G1. And the last one is the range to apply the function, D1 to J1. Where it falls behind Concat is it cannot be used to include a text or number that isn’t already a part of the excel sheet.
Final Thoughts
In data-loaded cases, it would be the Textjoin one would prefer over the other two. However, Concatenate in Excel makes sense to combine a few columns or if you want to include a common text argument. PS: Not just these, we have many more excel tips and tricks to make your life easy.
