Concatenate Not Working in Excel (3 Reasons with Solutions)

Often there are situations in Excel when concatenate is not working. Usually, MS Excel has three ways to join cell values. For example, we can use both the CONCATENATE and CONCAT functions to combine cell values. Besides, we can use the & (Ampersand) operator to join cell values in Excel. For example, we have a dataset containing the lists of first names and last names. Now, if all the formulas work properly, the following will be the result.

Concatenate Excel Not Working

But, there are some possible reasons for which the above-mentioned formulas and the operator do not work. So, in this article, we will explain the possible causes of failure and suggest how to solve those.


Download the Practice Workbook

You can download the practice workbook that we have used to prepare this article.


3 Reasons and Solutions of Concatenate Not Working in Excel

Reason 1: Concatenate in Excel Not Working If formula Cell Number Format is Text

Sometimes, cell values are not combined even if the formula is written correctly. Most of the time this happens when the formula cell is formatted as Text. For instance, we have applied the Number formatText’ to the formula cells of the above dataset. As a result, the following is our result.

Concatenate in Excel Not Working If formula Cell  Number Format is Text

Solution:

  • To solve this problem just change the cell format to ‘General’ and write the formulas in corresponding cells. Eventually, all the formulas will work and show combined texts.

Read More: How to Combine Text from Two or More Cells into One Cell in Excel (5 Methods)


Reason 2: Excel Concatenate Not Working If ‘Show Formulas’ Option is Active in Worksheet

Often, excel text-joining formulas do not show results in Excel. It may happen when the ‘Show Formulas’ option of the Excel ‘Formulas’ tab is active.

Excel Concatenate Not Working If ‘Show Formulas’ Option is Active in Worksheet

For example, if we activate the ‘Show Formulas’ option in our dataset, the following will be the result. Here, we only see the formulas, not the concatenated text.

Excel Concatenate Not Working If ‘Show Formulas’ Option is Active in Worksheet

Solution:

  • First, deactivate the ‘Show Formulas’ option.
  • Consequently, concatenate will work immediately. So, here are the joined texts.

Similar Readings :


Reason 3: Concatenate Not Working When Function Argument is Passed as Range

While using the CONCATENATE function, you cannot pass a range of cells as arguments. For example, type the below formula in Cell D5.

=CONCATENATE(B5:C5)

Concatenate Not Working When Function Argument is Passed as Range

As a consequence, texts are not joined in a single cell which means the function could not work properly.

Concatenate Not Working When Function Argument is Passed as Range

Solution:

You can use the CONCAT function to solve the problem mentioned above. Because the CONCAT function concatenates a list or range of text strings.

  • Initially type the following formula in Cell D5.
=CONCAT(B5:C5)

Concatenate Not Working When Function Argument is Passed as Range

  • Next, we will get the below result.

  • Later, you can use the Fill Handle (+) tool to copy the formula to the rest of the cells.

Related Contents: How to Concatenate Range in Excel (For Both Old and New Versions)


Conclusion

In the above article, I have tried to discuss the methods elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.


Related Articles

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo