You have come to the right place if you are looking for the answer or some unique tips to solve the problem when CONCATENATE function is showing the formula not the result. There are certain ways to solve the problem when CONCATENATE function is showing the formula not the result. This article will walk you through each and every step with appropriate examples. As a result, you can use them easily for your purpose. Let’s move on to the article’s main discussion.
5 Reasons and Solutions for Excel CONCATENATE Showing Formula but Not Result
I have a dataset of the first name and the last name of some employees and I have used the Excel CONCATENATE function to join them in a cell but the formula is not showing the outcome but the formula itself.
In this section, I will show you some possible reasons why CONCATENATE function is not showing the result. This article contains detailed explanations with clear illustrations for everything. I have used the Microsoft 365 version here. However, you may use any other version depending on your availability. Please leave a comment if any part of this article does not work in your version.
Reason 1: Equal Sign “=” Is Missing Before Formula
Also, you may forget to put an Equal sign, “=”, before the formula. Now, to check if this is true, Click on cells and check whether there is an equal sign in the formula bar before the formula or not. At this point, to fix this type of problem follow the steps below.
- In this case, click on the cells individually and add an Equal sign in front of the formula.
- And, you will see the formula is working perfectly.
Read More: Concatenate Not Working in Excel (3 Reasons with Solutions)
Reason 2: Cell Format Is Set to Text
If you are getting your formula as text, you may have the cell formats in Text. Now, to check this scenario, click on any cell in the range D5:D10 and check whether the cell format is Text or not. Consequently, if it is so, you may follow the steps below to solve the problem.
- First, select the cell and go to the Number Format options in the Home tab.
- Then, select the General
- Now, type the formula again in the cell and you will get the result.
- Finally, drag the Fill Handle to the rest of the cells.
Read More: How to Concatenate Names in Excel (5 Easy Ways)
- How to Concatenate Two Columns in Excel with Hyphen (9 Quick Ways)
- Concatenate Numbers with Leading Zeros in Excel (6 Methods)
- How to Concatenate Columns in Excel (8 Simple Methods)
- Combine Date and Text in Excel (5 Ways)
- How to Merge Rows in Excel (2 Easy Methods)
Reason 3: Show Formulas Option Is Enabled
Now, in this scenario, if you are getting your formula as text, you may have the Show Formulas options enabled in your Excel file. At this point, to check, if this is the case, go to the Formulas tab. Then, from Formula Auditing see if the Show Formulas option is enabled like in the screenshot below. Consequently, if it is so, follow the steps below to solve this problem.
- Go to the Formula tab in the top ribbon.
- Then, unselect the Show Formula option by just clicking on it
- Or, you can press CTRL + ` on your keyboard.
And, you will see that the formula cells show the formula output eventually.
Read More: Combine Text in Excel (8 Suitable Ways)
Reason 4: There Is Unnecessary Space Before Equal Sign in Excel
In this next scenario, you may have put an unnecessary space before the equal sign in the formula. Now, select any cell showing a formula and check if this is true in the formula bar like in the screenshot below. If after checking, you find this case to be true, you may follow the steps below to solve this problem.
- In this case, click on the cells and remove the extra spaces before the formula.
- Thus, the formula can work perfectly and will show the output.
Read More: How to Concatenate with Space in Excel (3 Suitable Ways)
Reason 5: Formula Starts with a Quote Symbol
If you are facing the issue of your formula being shown as text, you may have put a quote (‘) at the beginning of the formula in the formula bar. Now, to check if this case is true, click on the cells and observe the formula bar like in the screenshot below. So, if this scenario applies to you, you can fix the problem by following the steps below.
- Click on each cell having the formula and remove the quotient mark before the formula individually.
- After removing the quotient mark press enter Enter and you will see the formula is showing the output.
Read More: How to Concatenate Formula and Text in Excel (4 Examples)
In this article, you have found how to solve the issue of CONCATENATE function showing the formula in the cell, not the result. I hope you found this article helpful. You can visit our website, ExcelDemy, to get more Excel-related content. Please leave comments, suggestions, or queries if you have any in the comment section below.
- Concatenate Number and Text in Excel (6 Methods)
- How to Concatenate Cells but Keep Text Formatting in Excel
- Concatenate and Keep Number Format in Excel
- How to Concatenate Date/Day, Month, and Year in Excel
- Macro to Concatenate Multiple Columns in Excel (with UDF and UserForm)
- How to Concatenate Multiple Cells With Space in Excel (7 Methods)
- Concatenate Multiple Cells Based on Criteria in Excel (4 Methods)