Geekflare is supported by our audience. We may earn affiliate commissions from buying links on this site.
In Data Management Last updated: October 26, 2022
Share on:
Invicti Web Application Security Scanner – the only solution that delivers automatic verification of vulnerabilities with Proof-Based Scanning™.

Concatenate on Excel is outdated but still works for backward compatibility. Let’s check this, its updated replacement, applicable syntax, and much more.

Excel functions exist to make things easier. Concatenate is one such text function primarily about displaying multiple cell values in one.

Concatenate in Excel

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.

concatenate

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.

concatenate-with-spaces

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:

Concatenate2

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.

concatenate-vs-concat

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.

textjoin

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.

  • Hitesh Sant
    Author
    Hitesh works as a senior writer at Geekflare and dabbles in cybersecurity, productivity, games, and marketing. Besides, he holds master’s in transportation engineering. His free time is mostly about playing with his son, reading, or lying… read more
Thanks to our Sponsors
More great readings on Data Management
Power Your Business
Some of the tools and services to help your business grow.
  • Invicti uses the Proof-Based Scanning™ to automatically verify the identified vulnerabilities and generate actionable results within just hours.
    Try Invicti
  • Web scraping, residential proxy, proxy manager, web unlocker, search engine crawler, and all you need to collect web data.
    Try Brightdata
  • Monday.com is an all-in-one work OS to help you manage projects, tasks, work, sales, CRM, operations, workflows, and more.
    Try Monday
  • Intruder is an online vulnerability scanner that finds cyber security weaknesses in your infrastructure, to avoid costly data breaches.
    Try Intruder