Today I will be showing how you can join two or more texts or numbers into one single text value using the CONCATENATE function of Excel.
CONCATENATE Function of Excel (Quick View)
Download Practice Workbook
Excel CONCATENATE Function: Syntax and Arguments
Summary
- Joins two or more text values or numbers into one single text value.
- Have both array formula and non-array formula.
- Available from Excel 2003. You can call it the CONCAT function if you are in Office 365.
Syntax
The syntax of the CONCATENATE function is:
=CONCATENATE(text1,[text2],...)
Arguments
Argument | Required or Optional | Value |
text1 | Required | The first value to be joined. Can be any text value, number, cell reference or array of values. |
[text2] | Optional | The second value to be joined. Can be any text value, number, cell reference or an array of values. |
… | … | … |
… | … | … |
Notes:
- The arguments can be any text values, numbers, cell references or any array of values.
- In case you use an array of values as arguments, you have to press Ctrl + Shift + Enter as it will turn into an Array Formula.
- There is no particular limit to arguments. You can use as many arguments as you wish.
Return Value
Returns a joint text value combining all the arguments.
Excel CONCATENATE Function: 4 Examples
1. Using CONCATENATE Function with Texts
Look at the data set below. We have the Employee IDs, First Names, and Last Names of some employees of Mars Group.
Now we try to concatenate the first names and the last names into one single name.
The formula is easy. It is:
=CONCATENATE(C4,D4)
But you see, it looks gibberish. It will look better if you insert a space in between.
No problem. The space itself is a text value. So, concatenate the space with the names using this formula:
=CONCATENATE(C4," ",D4)
Now you see, the name looks better with a space in between.
Then drag or double click on the Fill Handle to copy the formula for the rest of the employees.
Formula | Output | Explanation |
=CONCATENATE(C4,” “,D4) | Morris Johnson | Concatenates the text values of the cells C4, D4, and a space “ ” into one single text. |
2. Using CONCATENATE Function with Numbers
Now we will try to join some numbers using the CONCATENATE function of Excel.
Let’s try to join the Employee IDs and the names into a single cell.
The formula will be:
=CONCATENATE(B4,", ",E4)
See, we have inserted a comma with space “, ” in-between for the text to look better.
Now if you want, you can drag the Fill Handle to copy the formula to the rest of the cells.
Formula | Output | Explanation |
=CONCATENATE(B4,”, “,E4) | 101, Morris Johnson | Joins the number in cell B4, a comma with a space and the text value in cell E4 into one single text. |
3. Using Array CONCATENATE Function
Now we will see how we can use array formulas with the CONCATENATE function.
Up to now, we have filled one cell first and then dragged the Fill Handle to the rest of the cells.
This time we will fill all the cells together, using an Array Formula.
Let’s try to concatenate the IDs, First Names, and Last Names of all the employees into one single cell using an Array Formula.
Select all the cells together and enter this Array Formula in the first cell:
=CONCATENATE(B4:B21,", ",C4:C21," ",D4:D21)
Then press Ctrl + Shift + Enter.
Explanation of the Formula
If we break the Array Formula, we will get 18 single formulas like this:
CONCATENATE(B4,", ",C4," ",D4)
CONCATENATE(B5,", ",C5," ",D5)
CONCATENATE(B6,", ",C6," ",D6)
…
…
…
CONCATENATE(B21,", ",C21," ",D21)
CONCATENATE(B4,", ",C4," ",D4)
joins the number in cell B4, a comma with a space, then the text in cell C4, then another space, then the text in cell D4 together.
Same for the rest of the cells.
Formula | Output | Explanation |
=CONCATENATE(B4:B21,”, “,C4:C21,” “,D4:D21) | 101, Morris Johnson
111, Richard Simpson 121, Steve Smith … … … 271, Maliha Muntaha |
Joins all the values in the arrays B4:B21, C4:C21 and D4:D21 together with a comma and a space in between. |
4. Using CONCATENATE function to MATCH Multiple Criteria Equal to a Value
One of the finest uses of the CONCATENATE function is that you can use it to match multiple criteria equal to a value in Excel.
Look at the data set below. We have the marks of some students in Physics, Chemistry, and Biology in a school named Sunshine Kindergarten.
Now we want to find out the student who got 100 in all three subjects.
Quite easy. The formula will be:
=INDEX(B4:E21,MATCH("100100100",CONCATENATE(C4:C21,D4:D21,E4:E21),0),1)
See, we have got the student with 100 in all three subjects, Usman Malik.
Explanation of the Formula
CONCATENATE(C4:C21,D4:D21,E4:E21)
joins all values of the arraysC4:C21, D4:D21
andE4:E21
into single text values.
For example, for the first student, it joins 78, 80 and 100 and returns “7880100”.
Similarly, for the student with 100 in all 3 subjects, it returns “100100100”.
MATCH("100100100",CONCATENATE(C4:C21,D4:D21,E4:E21),0)
returns the row number in the table where marks in all the three subjects are 100.INDEX(B4:E21,MATCH("100100100",CONCATENATE(C4:C21,D4:D21,E4:E21),0),1)
returns the value in the cell with row number equal to the output of the MATCH function and column number equal to 1 from the table array B4:E21.
This is the student who got 100 in all three subjects, that is Usman Malik.
Formula |
Output |
Explanation |
=INDEX(B4:E21,MATCH(“100100100”,CONCATENATE(C4:C21,D4:D21,E4:E21),0),1) |
Usman Malik |
First concatenates all the values of the arrays C4:C21, D4:D21 and E4:E21 into an array of joint text values. Then finds the row where “100100100” is in that array of joint values. Finally returns the cell content in that row with column number equal to 1. |
Note
- It is an Array Formula. So you must press Ctrl + Shift + Enter to insert this formula.
- This method only works for the cases where the criteria are equal to some values. If the criteria are greater than or less than some values, this method will not work.
For example, you can not identify the student who got greater than 95 in all subjects in this way.
- If more than one value satisfies all the criteria, you will get only the first value in this process. To get all the values that satisfy all the criteria, use the FILTER function instead.
Common Errors with Excel CONCATENATE Function
Error | When They Show |
#N/A! | This shows when the arguments are arrays instead of single values, and the lengths of all the arrays are not the same. |
Conclusion
In this way, you can use the CONCATENATE function of Excel to join two or more values into one single cell. Do you have any questions? Feel free to ask us.
Further Readings:
- How to Use TEXT Function in Excel (10 Examples)
- How to use SUBSTITUTE function in Excel (4 Examples)
- How to Use REPLACE Function in Excel (3 Suitable Examples)
- How to Use MID Function in Excel (3 Examples)
- How to Use SEARCH Function in Excel (3 Examples)
- How to Use LEN Function in Excel (Formula and VBA Code)