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.

**Table of Contents**hide

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