Concatenate Excel Columns

Concatenation of excel columns is very similar to concatenation the other data in excel, while concatenating text we provided texts in double-quotes as input but concatenating columns we will provide the cell reference or column reference, but this will give us the result in a single cell, we need to drag the formula to the rest of the cells for result.

Concatenate Excel Columns

Here, we will understand ways to concatenate the excel column using the concatenate functionConcatenate FunctionThe CONCATENATE function in Excel helps the user concatenate or join two or more cell values which may be in the form of characters, strings or more. Data may not always be as per our demand, and often we may need to join multiple excel columns of data to get the data in the form we want. A very trivial example is combining the first name & Last name to get the full name.

To get data into a structured format, sometimes combining multiple columns or combining columns with some values, which can be pre-defined or may come from a result based on some condition. We will see the various examples below, first starting with the general syntax.

Please see the screenshot for the general formula.

Concatenate Formula

The function is “CONCATENATE” and the arguments are any number of texts that you want to give it to it. The resulting value will be the combined value of all the arguments.

Important Point: From Excel 2016 onwards, you can see “CONCAT” function. It does the same task as “CONCATENATE”. Although “CONCATENATE” is also there in Excel 2016 for backward compatibility, there is no promise from Microsoft that they will continue to provide to do so. Therefore, for simplicity, I will explain everything using the “CONCATENATE” formula, since the syntax & arguments remain the same. Although, if you are using excel 2016 & above, I will request you to use the “CONCAT” function.

How to Concatenate Two Columns in Excel?

Below are the examples to Concatenate Excel Columns.

Concatenate Excel Columns Example #1

We will start with a simple one. Let’s suppose you have “First Name” in one column and “Last Name” in the second column and you want to get the full name by combining them.

  1. Please see the screenshot below:

    Concatenate Example 1

  2. Column D contains the formulas to combine them.

    Concatenate Example 1-1

  3. The Output is given below:

    Concatenate Example 1-2

  4. Drag the Formula to get the Result to the rest of the cells.

    Concatenate Example 1-3

  5. In Column D, you could see, I have combined, just the values from columns B & C, thus the result in column D is just a combination of them. But that doesn’t seem right. There should be a space in between the first name & last name. So, now when we use the formula in column E, we have added space when creating the formula.

    Concatenate Example 1-4

  6. So the result Looks like as given below:

    Concatenate Example 1-5

Example #2 – Concatenating Text String & Cell Value

Suppose we want to join these values to frame something meaningful.

Added an additional column for “Runs”.

Concatenate Example 2

In Column G, we created a formula that denotes the runs scored by that player along with the full name.

Concatenate Example 2-1

The Output is shown below:

Concatenate Example 2-2

Drag the Formula to rest of cells.

Concatenate Example 2-3

Another example may be, where the fixed string that I provided above (example “scored” & “runs”) can come from the result of a formula of a combination of both.

Concatenate Excel Columns Example #3

Suppose you always want to see today’s date in cell B2. See the formula shown. We have used the “CONCATENATE”, “TEXT” Function, and “TODAY” function. TODAY function in excelTODAY Function In ExcelToday function is a date and time function that is used to find out the current system date and time in excel. This function does not take any arguments and auto-updates anytime the worksheet is reopened. This function just reflects the current system date, not the more gives today’s date, however, it gives the result in an integer, which we need to convert into text & then into date format.

concatenate excel columns Example 3

So, the “TEXT” function in excel converts the output of the “TODAY” function in the format “mm-dd-yyyy” and then the concatenate function joins them together along with “Today is” & “ “ (space) and gives the result as given below.

concatenate excel columns Example 3-1

Besides the “CONCATENATE” function, another operator is “&” which you can use to combine textsCombine TextsTo combine text from two or more cells into one cell, select the cell to begin with, then type "=" and select the desired cell to combine, then type "&" and use quotation marks with a space enclosed, and finally select the next required cell to connect and press the enter more. You can do all the above examples using “&” also.

When compared to “CONCATENATE” function, the only difference is “&” has no limit on the number of strings that can be used, while CONCATENATE has a limit of 255 arguments and 8,192 characters.

No difference in speed of calculations either. So, it all boils down to your choice, comfort, and ease of use.

Concatenate Excel Columns Example #4

The last example that we will see is where we want to concatenate the columns based on some special characters like line breaks, forward slash, asterisk, etc. based on their ASCII codes (use Char function in excelChar Function In ExcelThe character function in Excel, also known as the char function, identifies the character based on the number or integer accepted by the computer language. For example, the number for character "A" is 65, so if we use =char(65), we get more).

The ASCII codes for a line break is CHAR(10), for forwarding slash, it is CHAR(47) and for Asterisk, it is CHAR(42). Now, let’s use these.

Below is the Data. we want to Combine them using Forward slash (/).

concatenate excel columns Example 4

Here we used the Formula to Combine them.

concatenate excel columns Example 4-1

It combines the data using a slash.

concatenate excel columns Example 4-2

You can see from the screenshot, that you can achieve the same results using 4 different formulas.

concatenate excel columns Example 4-3

Things to Remember About Concatenate Two Columns in Excel

  • There is a limit of 255 strings that you can concatenate at one time. In terms of characters, it is 8,192.
  • The result will be always a text string, even if all the arguments are numbers. For example, CONCATENATE(42,42) will give “4242”. i.e. the format will always be text.
Concatenate Text string
  • Arrays or range of cells is not recognized as an argument. For e.g., you need to provide CONCATENATE(A1,A2, A3) instead of CONCATENATE(A1: A3)
Concatenate array or range of cells
Concatenate invalid Argument error
  • “&” operator is an alternative to the concatenate function. It can do everything that the “CONCATENATE” function does, without “CONCATENATE” function limitations of 255 arguments.

You can Download this Concatenate 2 Columns Excel template here – Concatenate Columns Excel Template

Recommended Articles

This was guide to Excel Concatenate Columns. Here we discuss how to concatenate columns in Excel with practical examples and template for downloading. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *