Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use CONCATENATE Function in Excel (4 Easy Methods)

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.

Excel CONCATENATE Function Overview


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:

CONCATENATE function arguments

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)

Using CONCATENATE Function with Texts

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

Using CONCATENATE Function with Texts, adding space in between

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

copy a formula using fill handle


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.

Using CONCATENATE Function with Numbers

  • 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


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

Using Array CONCATENATE Function for Ranges

πŸ”Ž 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)

Using CONCATENATE Function to MATCH Multiple Criteria Equal to a Value

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.

Excel CONCATENATE Function Not Working Due to Show Formulas Button On

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


Further Readings

Rifat Hassan

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 the 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