Excel offers its users a range of functions to use in their workbooks. Among many functions with different features, the concatenate function in Excel combines data in multiple cells into a singular cell. Although the function is very simple to use, many new users may face issues in correctly using this function.
Unfortunately, if you face problems using the concatenate function, we’re here to help! In this article, we will be discussing why concatenate may not be working for you and how you can fix it!
Why is My Excel CONCATENATE Not Working?
There are many rookie errors even the most advanced users of Excel may face when it comes to using the concatenate function. While entering a function, you must consider everything ranging from errors in typing to formatting your cell contents. Before sliding to the solutions, let’s examine why concatenate may not work.
- Error in Typing: You may have accidentally misspelled the function. As the function’s name is comparatively longer, it can be tricky to spell it correctly.
- Format: Many users may not consider the format of the cell contents while using Excel. However, this plays a vital role while using a function. Your function won’t work if you’ve set the cell format containing the formula as text.
- Show Formula is Enabled: Excel offers its users the feature to only show the formula. When this feature is active, Excel will display the formula used to calculate the content instead of displaying the calculated results.
- Syntax Error: The concatenate function cannot pass arguments as a range. All arguments must be included with the comma symbol (,) separating each entity.
How to Fix the CONCATENATE Function Not Working?
It is quite simple to rectify the mistakes mentioned above. You can try solutions ranging from correcting spelling errors to correcting syntax. You can refer to the relevant solutions to the issue you suspect. Refer to the solutions mentioned below to fix the issue with the concatenate function in MS Excel.
Use Correct Spelling
Most users rush while entering functions they are familiar with. Especially in workbooks with large data, any user may have simply overlooked the spelling error. If you received the #NAME? error, you have mostly spelled the function wrong, or the cell you mentioned is empty.
Use Correct Formatting
Set the format of the cell with the concatenate function as General. When you correctly set the format, Excel can execute the command. Follow these steps to change the formatting of your cell to General:
- Launch MS Excel to open your workbook.
- On the Home ribbon, locate the Number section.
- Select the drop-down menu and choose General.
Disable Show Formula
You may have forgotten to disable the Show Formula tool on your workbook. You will only view the calculated result when you manually disable this tool on Excel. Here are the steps you can refer to disable Show Formula on MS Excel.
- Open your Excel Workbook.
- From the menu bar, select Formula.
- On the Formula ribbon, locate the Formula Auditing section.
- If selected, deselect the Show Formula option to view the calculated results.
Use Proper Syntax
You need to pass arguments individually while using the CONCATENATE function. Each argument must be separated using the comma (,) symbol. The function will be invalid if you’ve used any other symbol or passed the arguments as a range. Similarly, all functions begin with the equals sign (=). Be sure to have used all of these correctly.
Here is an example of how the CONCATENATE function is properly used:=CONCATENATE(A2,B2,C2,“text”)
Other Ways to Combine Data: Ampersand
You can also use Ampersand (&) to combine data from multiple cells into one. Unlike CONCATENATE, Ampersand is an operator. In comparison to other functions, users find the operator more convenient to use as it leaves less room for syntax error and saves more time.
Here is how you can use the Ampersand to combine your data:=A2 & B2
There are other combining techniques in MS Excel besides the CONCATENATE function and Ampersand. Learn more about how you can combine cells in Excel without losing data.
More on CONCATENATE Function
Like every function, the CONCATENATE function has its own set of protocols you must remember when in use. When using this function, you will leave less room for error when you keep these rules in mind.
Below, we’ve listed all the things you need to keep in mind while using the CONCATENATE function:
- You cannot pass more than 255 strings using a single function.
- You cannot pass more than 30 arguments in a single function.
- Excel will display the #VALUE error if any argument is wrong.
- You must pass at least one argument in text format to return a value.
- The result passed by the function is always a text string.
- You cannot pass arguments as a range.
- Texts need to be kept inside quotation makes in the CONCATENATE formula. However, this does not apply to numbers.
Frequently Asked Questions (FAQ)
How Do You Activate the CONCATENATE Function?
If you want Excel to register your data as a function, you must use the equals sign (=) in the beginning. Similarly, the CONCATENATE function only works if you add an equal (=) sign before it; =CONCATENATE(
Why Are the Ribbon Icons Greyed Out?
Most ribbon icons get greyed out when you enter the ‘Editing Mode’ in Excel. You may have accidentally hit the F2 button on your keyboard that triggered this change. To revert this, you can simply select a different cell in Excel.
