Concatenate Not Working in Excel (3 Reasons with Solutions)

Get FREE Advanced Excel Exercises 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

However, 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.


Concatenate Not Working in Excel: 3 Reasons and Solutions

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 Concatenate with Delimiter in Excel


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.

Read More: How to Concatenate with Space in Excel


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.

Read More: How to Concatenate Apostrophe in Excel


Download the Practice Workbook

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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo