How to correct a #VALUE! error in the CONCATENATE function

Important: In Excel 2016, Excel Mobile, and Excel Online, CONCATENATE has been superseded by the CONCAT function. Although CONCATENATE is still available for backward compatibility, you should consider using CONCAT as it is a more robust function.

One of the most common scenarios where you encounter the #VALUE! error with CONCATENATE is when you try to build a string that includes a cell reference that has an error, such as #VALUE!.

In the following example, we want to concatenate D2, E2, and F2, where E2 has a #VALUE! error.


The error in E2 causes the function to display the #VALUE! error. In order for it to work successfully, you will need to replace the error with a value other than the error. You can build a formula that checks for an error in the cell reference, and if it does, display the text string where a 0 appears instead of the error (or you can substitute your own text for 0, but you’ll need to wrap the text in quotes – “Your Text”).

A formula like this will work:


IF and ISERROR functions used as a workaround to concatenate a string with the #VALUE! error

How does that work? The IF function uses the IFERROR function to determine if there is an error in E2, and if there is, the CONCATENATE function combines the values in D2, E2 and F2 (the function replaces the error with a 0), and displays the resulting text string. Note the formula also uses spaces (“ “) to help separate the values.

Fix the #VALUE! error with CONCATENATE in Excel 2003 and older versions

If you are getting the #VALUE! error in Office Excel 2003 or older versions, you will need to uncheck the Transition Formula Evaluation (TFE) option in Excel. Follow the steps outlined in #VALUE! error when concatenating cells to resolve the issue.

Do you have a specific function question?

Post a question in the Excel community forum

Help us improve Excel

Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice.

See Also

Correct a #VALUE! error


CONCAT function

Overview of formulas in Excel

How to avoid broken formulas

Use error checking to detect errors in formulas

All Excel functions (alphabetical)

All Excel functions (by category)

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!