Table Of Contents

arrow

Concatenate Excel Columns

Publication Date :

Blog Author :

Edited by :

Reviewed by :

Concatenate Excel Function Explained in Video

 

How to Concatenate Two Columns in Excel?

Below are examples of concatenating Excel columns.

Concatenate Excel Columns Example #1

We will start with a simple one. 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. We have the Column D that contains the formulas to combine them.


    Concatenate Example 1-1

  3. The output is given below:


    Concatenate Example 1-2

  4. We need to drag the formula now to get the result to the remaining cells.


    Concatenate Example 1-3

  5. In Column D, we can see that we have combined just the values from columns B and C. Thus, column D's result is just a combination of them. But that does not seem right. There should be a space in between the first name and 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 may look 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.

We have added a column for "Runs."

Concatenate Example 2

We have created a formula in "Column G" 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

Now, we will drag the formula to the remaining cells.

Concatenate Example 2-3

Another example may be where the fixed string that we provided above (example "scored" and "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. You may view the formula shown. We have used the "CONCATENATE," "TEXT" function, and "TODAY" function. The TODAY function in excel gives today's date. However, it provides the result in an integer, which we need to convert into text and 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 shown below.

concatenate excel columns Example 3-1

Besides the “CONCATENATE” function, another operator is “&” which you can use to combine texts. You can do all the above examples using “&” also.

Compared to the "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 the 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 need to 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 (Char function in excel).

The ASCII code for a line break is CHAR(10), for forwarding slash, it is CHAR(47), and for asterisk, it is CHAR(42). So now, let us use these.

Below shown is the data. We want to combine them using the forward-slash (/).

concatenate excel columns Example 4

Here, we used the formula to combine them.

concatenate excel columns Example 4-1

As a result, 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 four 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 we can concatenate at a time. In terms of characters, it is 8,192.
  • The result will always be a text string, even if all the arguments are numbers. So, 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. E.g., you need to provide CONCATENATE(A1,A2, A3) instead of CONCATENATE(A1: A3).
Concatenate array or range of cells
  • If any argument to this function is invalid, it will generate an excel error.
Concatenate invalid Argument error
  • "&" operator is an alternative to the concatenate function. It can do everything that the "CONCATENATE" function does without the "CONCATENATE" function limitations of 255 arguments.

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