Important: In newer versions of Excel, Excel Mobile, and Excel for the web, 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(ISERROR(E2),CONCATENATE(D2," ",0," ",F2))
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.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in Communities.
