Excel CONCATENATE Function – Combine Columns in Cells

The English meaning of the word ‘Concatenate‘ is to join two or more things, and the same applies to excel as well. The CONCATENATE function in excel is the formula that will solve this excel query – “How to combine texts, numbers, values, and characters in two or more cells into one single cell?

In this tutorial, we would learn how to use the CONCATENATE Excel formula to join cell values in excel along with its syntax, arguments, and some examples.

Here we go 😎

When to Use CONCATENATE Function in Excel

CONCATENATE formula in excel is a useful function to combine/join values, texts, numbers, and strings in two or more cells into one single excel cell. This function forms part of the ‘Text function group’ in excel.

This formula returns a joined value/string.

Syntax and Arguments

=CONCATENATE(text1, [text2], …)

The CONCATENATE function accepts maximum of 255 input arguments (i.e. a total 8192 characters):

  • text1 – In this argument, enter the first text to join.
  • [text2] – In this optional argument, enter the second text to join.

In a similar way, you can enter up to 255 such text arguments each separated by a comma, like this:

=CONCATENATE(text1, [text2], [text3], [text4], …)

Note that, the CONCATENATE function’s input argument can accept following types of values:

  • text, numbers, strings, values
  • a cell reference, or
  • result of some other formula

Examples of Using CONCATENATE Function in Excel

In this section, we would learn how the CONCATENATE function works in excel with the help of few examples.

Ex. 1 # Combining Texts/Words in Excel using CONCATENATE Function

This is a basic example showing the working of CONCATENATE excel formula. Suppose you want to join the words “Mc” and “Donalds” in excel.

To achieve this, simply specify the two words as CONCATENATE function’s input arguments, like this:

=CONCATENATE("Mc","Donalds")

As a result, you would get a combined word in excel “McDonalds”.

CONCATENATE Texts in Excel

Similarly, the formula =CONCATENATE(“Excel”,”Unlocked”) with result into a combined text “ExcelUnlocked”.

CONCATENATE Words in Excel

Note that, when you use the CONCATENATE function, excel does not put any delimiter like space, colon, semi-colon and so on.

To combine two texts with space in between two words, you need to manually put the space within double-quotes like this:

=CONCATENATE("Mc"," ","Donalds")
CONCATENATE Text with Space Between Words

Ex. 2 # Combining Cell Values Using Cell Reference

Instead of manually typing the texts/words inside the function argument within double-quotes, you can also use the cell reference.

Cell B1 contains the first word, and B2 contains the second word. To join the texts in these two cells with space in between, use the following formula:

=CONCATENATE(B1," ",B2)
CONCATENATE Function Using Cell Reference

Ex. 3 # Combining Cell Value and Text Characters

We can also combine text characters and cell values using a single CONCATENATE formula. Let us see how.

Infographic - CONCATENATE Formula Function in Excel

Below image contains the list of product codes.

List of Material Codes

Now, suppose you want to write the text ‘PROD-‘ before each of these material codes.

To achieve this, use the following formula in cell B2 and copy it down to the below cells.

=CONCATENATE("PROD-",A2)
CONCATENATE Text and Cell value in Excel

As a result, excel inserts the text ‘PROD-‘ before each of the material codes.

Ex. 3 # Combining Text with Formula Result in Excel

Now, let us see how to combine a text and a formula result in excel using the excel CONCATENATE function.

Suppose, you want to write today’s date in a cell prefixed with the text – ‘Today’s date is ‘, such that the date changes every day.

To achieve this, we shall use the TODAY excel function to find today’s date and then concatenate it with the text ‘Today’s date is’ using CONCATENATE function.

See the below formula:

=CONCATENATE("Today's Date is"," ",TEXT(TODAY(),"DD-MM-YYYY"))
CONCATENATE Text and Formula Result in Excel

The excel TEXT function (in the above example) converts today’s date serial number into a date format (dd-mm-yyyy).

Ex. 4 # Combine Numbers in Excel Using CONCATENATE formula

In the above examples, we learned how to combine text, characters, and strings in excel. In this example, let us learn to combine numbers in several cells into one cell.

Below image contains a list of numbers in excel to join in excel.

List of Numbers to Join in Excel

Simply use the below formula to join numbers in excel using the CONCATENATE excel function.

=CONCATENATE(A2,B2,C2,D2,E2,F2,G2)
Joining Numbers in Excel Using CONCATENATE function

Note that, the result of the CONCATENATE function is always a text string. It means that even if you combine the numbers (as shown above) using CONCATENATE formula, the result is not a number but a text string.

Thank You 🙂

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.