Why Concatenate Isn’t Working in Excel (3 Common Reasons and Solutions)

We’ll use the below dataset containing the lists of first names and last names to investigate why Concatenation is not working:

Reason 1: Formula Cell Number Format is Text

Sometimes, even when you’ve written the formula correctly, cell values don’t combine as expected. This often occurs when the formula cell is formatted as “Text.” For example, if you’ve applied the “Text” number format to the formula cells in your dataset, the concatenation won’t work as intended. The result might look like this:

Solution:

To resolve this issue:

Change the cell format from “Text” to “General.”
Rewrite your formulas in the corresponding cells.
Your formulas will now work correctly, and the combined texts will display as expected.

Reason 2: “Show Formulas” Option is Enabled

Excel provides a feature that allows you to display only the formulas in your worksheet. If you’ve accidentally activated the “Show Formulas” option, you’ll see the formulas themselves rather than the concatenated text. Here’s an example:

Solution:

To fix this, simply deactivate the “Show Formulas” option. Once you do, your concatenation will work as intended, and you’ll see the combined text instead of the raw formulas.

Reason 3: Passing a Range as an Argument in CONCATENATE Function

The CONCATENATE function doesn’t accept a range of cells as arguments. If you try to concatenate a range directly, it won’t work as expected. For instance, typing the following formula in cell D5 won’t yield the desired result:

=CONCATENATE(B5:C5)