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.
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 format ‘Text’ to the formula cells of the above dataset. As a result, the following is our result.
- 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.
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.
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.
- First, deactivate the ‘Show Formulas’ option.
- Consequently, concatenate will work immediately. So, here are the joined texts.
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.
As a consequence, texts are not joined in a single cell which means the function could not work properly.
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.
- 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.
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.