Concatenate in Excel

One of the most integral parts of formatting data in Excel would be to format numbers and texts as per your requirements. However, navigating string texts on the spreadsheet might be difficult with basic conventional formulas. 

String texts are basically words or texts that might be a part of some cells. These might appear as product names, barcodes, inventory identification texts or cells which have both words and numbers. 

In this regard, you would need to use concatenate in Excel. By using concatenate in Excel, you will be able to combine variable parts of your spreadsheet. This will include things like words and texts, special characters and even special character strings.

How to Use Concatenate in Excel? 

At the very outset, it is important to make one distinction when working with concatenate in Excel formulas. This has to do with the version of Excel you might be working on.

If you are working on a version of Excel older than Excel 2016, then you would need to write the formula in Excel as CONCATENATE. Now, if you are using Excel 2016 onwards, then the formula that you can put in can be either CONCATENATE or CONCAT. Either way, both those alternatives would be considered valid.

There are a few other things that you would have to keep in mind if you wish to use concatenate in Excel:

1. Irrespective of the kind of data you are playing within the ambit of the CONCATENATE formula in Excel, it will always return a text string. So, even if the data is a text or number, the resultant would be a string. This could potentially change the way other functions use the resultant data.

2. All special characters that you might want to include should be placed under quotation marks. Failure to do that could make Excel consider the characters as a part of the formula.

3. For CONCATENATE formulas to work in Excel, there needs to be at least one string. Concurrently, you can’t use a “:” to highlight a range of cells. For instance, you can’t make an array like “B3:B34” to signify a range of cells. You will need to mention each of the cells you are integrating, like “B3, B4, B5, B6, B7, B8 etc”.

4. There is an upper limit on the maximum number of strings or characters that you can put under the formula. You can put a total of 255 strings or 8192 characters.

How to Concatenate Two Columns in Excel

To understand how you can concatenate two columns in Excel, let's take a scenario. 

Consider you have received a spreadsheet of names for a wedding list. Since it was exported out of a Google form, the names and surnames are placed in different columns. Now, to ensure that the names and concurrent surnames are combined to produce a final list, you would need to use the CONCATENATE formula with a comma. The syntax for concatenate in Excel, in this case, would be =CONCATENATE(Cell 1, Cell 2)

But, if you notice the formula carefully, you’ll realize that what the syntax used actually did is merge the name and surname without a gap in between. To ensure that the resulting output has a space in between, the formula must be tweaked in accordance. 

So, the syntax for concatenate in Excel with space would be =CONCATENATE(Cell 1,“ ”, Cell 2). Given your requirements, whether to integrate with a space or without, this is the way you can concatenate two columns in Excel.

How to Concatenate Cells With Comma, Dash, or Special Characters

An integral part of using CONCATENATE in Excel is the customizations the function comes with. You can not only join two columns; you can also add any special character that you would need, depending upon your requirement, between these two parts of the string. If you need a comma, for instance, integrated between the two halves, you can do so by using the following syntax: =CONCATENATE(Cell 1,“ , ”,Cell 2)

Similarly, if you had to add a dash (“-”) or and (“&) between the two columns, you could just use the syntax =CONCATENATE(Cell 1, “ Special character ”, Cell 2).

Combining Columns in Excel With a Dash

Columns are combined using CONCATENATE in Excel for a variety of reasons. The primary among them is to create strings that could potentially help you identify goods from a single code. 

For instance, suppose you are running a car dealership. The three commonly required data for you would be the model name, the model year, and the color of the car. Now, when you get your spreadsheet, which has all these three pieces of information in disparate columns, you can use CONCATENATE to join the info up and create a string. This reduces your time in navigating across different columns to find information pertaining to the same object.

As you can see from the image above, using the dash (-) to combine 3 columns in Excel into a single column helps you gain a minimalistic overview of the entire information conveyed in the 3 columns. The syntax for using this formula is: =CONCATENATE(Cell 1, “-”, Cell 2, “-”, Cell 3). 

Depending on the number of columns you wish to combine, you can keep adding cells in the alternate style, as shown above.

Using Concatenate Function With Dates

Now that you have a cursory idea of how to go about merging columns using CONCATENATE in Excel, it is important to understand how to work with dates. 

Dates and date timelines are a bit tricky when it comes to combining columns, primarily because they might not always work using the methods mentioned above. Consider the example we were using above. Now, instead of an inventory, what if the dataset were to represent the dates on which the cars were sold? In that scenario, you would benefit from a string that identifies the car and also includes the date it was sold. Here, you will need to use the ampersand (&) symbol.

Firstly, you would need to fix the date given in a particular format. For instance, the date format in the image given is represented in the dd-mm-yy format. If you were to directly add the date as a part of the string, the result would be as follows:

In the above image, as you can well realize, the date is not correctly represented. In this regard, you will need to use the Text function which will allow you to integrate the date as per your requirement. Depending upon your requirement, you can formulate the date to be in the format of dd-mm-yy, yy-mm-dd or mm-dd-yy. After integrating the date, you can then use the Ampersand to integrate the rest of the cells or columns. The formula can be used as follows.

Integrating Line Breaks in Excel

Another major drawback of CONCATENATE in Excel is single lines. No matter how much data is added to the string, the CONCATENATE formula in Excel will always represent the output in one single line. This might pose a problem for certain kinds of datasets, especially with things like addresses. 

To include a line break, you will need to integrate the CHAR function. The function will use the ASCII code for a line break and represent that on the output, thereby putting a line break in the final output shown. You must use the function CHAR(10) to indicate a line break. 

The syntax for the formula would be: =CONCATENATE(Cell 1, CHAR(10), Cell 2, CHAR(10), Cell 3). 

Summing Up

CONCATENATE in Excel has emerged as a top tool for segregating and combining data for diverse datasets. Given its effortless flexibility in combining with other formulas the function is an important one to be familiar with. In this regard, you must also take a look at the opposite of CONCATENATE in Excel, which is the Flash Fill option. The Flash Fill option is used to split existing datasets. Combining the two formulas would make you a master of arranging data on a spreadsheet.

Want to learn more about how Excel makes working with data easier? Check out Excel Free Online Courses with Certification from upGrad.

FAQs

1. What is the CONCATENATE and left function in Excel?

The CONCATENATE function when combined with the left function in Excel can be used to select a particular number of characters or special symbols from the left side of a string, and then combined to a fresh string.

2. How do I merge 3 Columns in Excel?

You can merge 3 or multiple columns in an Excel sheet using the CONCATENATE function with a dash. The syntax for the formula would be  =CONCATENATE(Cell 1, “-”, Cell 2, “-”, Cell 3). 

3. How do I combine multiple cells into one?

You can combine multiple cells in one using the CONCATENATE formula. Subject to the kind of data you are going to combine, most basic cells can be combined using the following syntax: =CONCATENATE(Cell 1, Cell 2, Cell 3, Cell 4).