Rifat Hassan
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)
The following image is a quick overview of using the Excel CONCATENATE function.
Download Practice Workbook
You can download the following Excel practice workbook to practice while reading this article.
Introduction to CONCATENATE Function in Excel (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.
⦿ Syntax:
The syntax of the CONCATENATE function is:
=CONCATENATE(text1,[text2],…)
⦿ Arguments:
The arguments can be in any format, e.g. text, number, cell reference, or an array of values.
| Argument | Required/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 is to be joined. Can be any text value, number, cell reference, or array of values. |
⦿ Return Value:
Returns a joint text value combining all the arguments.
Read More: How to Use TEXT Function in Excel (10 Examples)
4 Examples of Using CONCATENATE Function in Excel
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(C5,D5)
- But you see, it looks gibberish. It would 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(C5," ",D5)
This formula concatenates the text values of the cells C5, D5, and a space “ ” into one single text.
- 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.
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.
- For this, the formula will be:
=CONCATENATE(B5,", ",E5)
This formula joins the number in cell B5, a comma with a space, and the text value in cell E5 into one single text.
- See, we have inserted a comma with the 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.
Similar Readings
- How to Use CODE Function in Excel (5 Examples)
- Use Excel EXACT Function (6 Suitable Examples)
- How to Use FIXED Function in Excel (6 Suitable Examples)
- Use CLEAN Function in Excel (10 Examples)
- How to use TRIM function in Excel (7 Examples)
3. Using CONCATENATE Function for Ranges
- Note that CONCATENATE function cannot recognize an array, i.e. if you want to join values in cells A1, A2, A3, and A4, you cannot write the argument as (A1:A4), rather it must be (A1,A2,A3,A4). You have separate the inputs with commas in the function.
- But you can separate arrays as arguments. In this article, we will see how to do that.
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(B5:B22,", ",C5:C22," ",D5:D22)
- Then press Ctrl + Shift + Enter. (or just press Enter if you are an Excel 365 user like me.)
🔎 Formula Breakdown:
If we break the Array Formula, we will get 18 single formulas like this:
- CONCATENATE(B5,”, “,C5,” “,D5)
- CONCATENATE(B6,”, “,C6,” “,D6)
- CONCATENATE(B7,”, “,C7,” “,D7)
…
…
…
- CONCATENATE(B22,”, “,C22,” “,D22)
CONCATENATE(B5,”, “,C5,” “,D5) joins the number in cell B5, a comma with a space, then the text in cell C5, then another space, then the text in cell D5 together. Same for the rest of the cells.
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(B5:E22,MATCH("100100100",CONCATENATE(C5:C22,D5:D22,E5:E22),0),1)
See, we have got the student with 100 in all three subjects, Usman Malik.
🔎 Formula Breakdown:
- CONCATENATE(C5:C22,D5:D22,E5:E22) joins all values of the arrays C5:C22, D5:D22 and E5:E22 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(C5:C22,D5:D22,E5:E22),0) returns the row number in the table where marks in all three subjects are 100.
- INDEX(B5:E22,MATCH(“100100100”,CONCATENATE(C5:C22,D5:D22,E5:E22),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 B5:E22.
This is the student who got 100 in all three subjects, is Usman Malik.
Note:
- It is an Array Formula. So you must press Ctrl + Shift + Enter to insert this formula.
- This method only works for 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.
Read More: How to Use FIND Function in Excel (7 Suitable Examples)
Common Errors with CONCATENATE Function of MS Excel
| 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. |
| #VALUE! | This shows when the arguments are invalid. |
| #NAME? | This shows when quotation marks are missing for Text arguments. |
Some More Reasons for Excel CONCATENATE Not Working:
- Check if the Show Formulas option is on in the Formula Auditing section of the Formulas tab.
- If you pass the inputs as range, the function will not work. However, you can try the CONCAT function for this.
💬 Quick Notes
- CONCATENATE function is capable of concatenating 255 strings (8,192 characters) in one formula.
- We are using Excel 365 to write this article and had no issues with numbers while concatenating.
However, you can face difficulties when concatenating all numbers, in Excel backdated versions.
- This function may not be available in future Excel versions. So Microsoft Excel recommends using the CONCAT function instead. It’s available in Excel since 2016. Also available in Excel mobile and web versions.
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.
