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”.
Similarly, the formula =CONCATENATE(“Excel”,”Unlocked”) with result into a combined text “ExcelUnlocked”.
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")
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)
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.
Below image contains the list of product 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)
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"))
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.
Simply use the below formula to join numbers in excel using the CONCATENATE excel function.
=CONCATENATE(A2,B2,C2,D2,E2,F2,G2)
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 🙂