How to Use CONCATENATE Function in Excel (4 Examples)

Quick View of Excel CONCATENATE Function

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)

Quick View of Excel CONCATENATE Function


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

Syntax of CONCATENATE Function

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.

Data Set in Excel

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)

CONCATENATE Function with Texts

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)

CONCATENATE Function with Texts and Space

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.

Dragging Fill Handle with CONCATENATE Function

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)

CONCTENATE Function with Numbers

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.

CONCATENATE Function with number and comma

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.

Array CONCATENATE Formula

Explanation of the Formula

If we break the Array Formula, we will get 18 single formulas like this:

  1. CONCATENATE(B4,", ",C4," ",D4)
  2. CONCATENATE(B5,", ",C5," ",D5)
  3. CONCATENATE(B6,", ",C6," ",D6)

  1. 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.

New Data Set in Excel

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)

Matching Multiple Criteria with CONCATENATE function

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 arrays C4:C21, D4:D21 and E4: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:

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo