Why Is My Excel CONCATENATE Not Working? Here’s How To Fix It.
Excel affords its customers a spread of features to make use of of their workbooks. Among many features with totally different options, the concatenate operate in Excel combines information in a number of cells right into a singular cell. Although the operate could be very easy to make use of, many new customers might face points in accurately using this operate.
Unfortunately, if you happen to face issues using the concatenate operate, we’re right here to assist! In this text, we will probably be discussing why concatenate is probably not working for you and how one can repair it!
Why is My Excel CONCATENATE Not Working?
There are many rookie errors even essentially the most superior customers of Excel might face in relation to using the concatenate operate. While getting into a operate, you could think about the whole lot starting from errors in typing to formatting your cell contents. Before sliding to the options, let’s study why concatenate might not work.
- Error in Typing: You might have by chance misspelled the operate. As the operate’s title is relatively longer, it may be tough to spell it accurately.
- Format: Many customers might not think about the format of the cell contents whereas using Excel. However, this performs a significant function whereas using a operate. Your operate received’t work if you happen to’ve set the cell format containing the method as textual content.
- Show Formula is Enabled: Excel affords its customers the characteristic to solely present the method. When this characteristic is lively, Excel will show the method used to calculate the content material as a substitute of displaying the calculated outcomes.
- Syntax Error: The concatenate operate can not move arguments as a spread. All arguments have to be included with the comma image (,) separating every entity.
How to Fix the CONCATENATE Function Not Working?
It is sort of easy to rectify the errors talked about above. You can attempt options starting from correcting spelling errors to correcting syntax. You can seek advice from the related options to the problem you watched. Refer to the options talked about beneath to repair the problem with the concatenate operate in MS Excel.
Use Correct Spelling
Most customers rush whereas getting into features they’re accustomed to. Especially in workbooks with massive information, any consumer might have merely missed the spelling error. If you obtained the #NAME? error, you’ve got principally spelled the operate mistaken, or the cell you talked about is empty.
Use Correct Formatting
Set the format of the cell with the concatenate operate as General. When you accurately set the format, Excel can execute the command. Follow these steps to vary the formatting of your cell to General:
- Launch MS Excel to open your workbook.
- On the Home ribbon, find the Number part.
- Select the drop-down menu and select General.
Disable Show Formula
You might have forgotten to disable the Show Formula software in your workbook. You will solely view the calculated end result if you manually disable this software on Excel. Here are the steps you’ll be able to seek advice from disable Show Formula on MS Excel.
- Open your Excel Workbook.
- From the menu bar, choose Formula.
- On the Formula ribbon, find the Formula Auditing part.
- If chosen, deselect the Show Formula choice to view the calculated outcomes.
Use Proper Syntax
You have to move arguments individually whereas using the CONCATENATE operate. Each argument have to be separated using the comma (,) image. The operate will probably be invalid if you happen to’ve used another image or handed the arguments as a spread. Similarly, all features start with the equals signal (=). Be certain to have used all of those accurately.
Here is an instance of how the CONCATENATE operate is correctly used:
Other Ways to Combine Data: Ampersand
You may also use Ampersand (&) to mix information from a number of cells into one. Unlike CONCATENATE, Ampersand is an operator. In comparability to different features, customers discover the operator more handy to make use of because it leaves much less room for syntax error and saves more time.
Here is how you should use the Ampersand to mix your information:
=A2 & B2
There are different combining methods in MS Excel apart from the CONCATENATE operate and Ampersand. Learn more about how one can mix cells in Excel with out shedding information.
More on CONCATENATE Function
Like each operate, the CONCATENATE operate has its personal set of protocols you could keep in mind when in use. When using this operate, you’ll depart much less room for error if you keep these guidelines in thoughts.
Below, we’ve listed all of the belongings you want to bear in mind whereas using the CONCATENATE operate:
- You can not move greater than 255 strings using a single operate.
- You can not move greater than 30 arguments in a single operate.
- Excel will show the #VALUE error if any argument is mistaken.
- You should move not less than one argument in textual content format to return a price.
- The end result handed by the operate is at all times a textual content string.
- You can not move arguments as a spread.
- Texts should be saved inside citation makes within the CONCATENATE method. However, this doesn’t apply to numbers.
Frequently Asked Questions (FAQ)
How Do You Activate the CONCATENATE Function?
If you need Excel to register your information as a operate, you could use the equals signal (=) at first. Similarly, the CONCATENATE operate solely works if you happen to add an equal (=) signal earlier than it;
Why Are the Ribbon Icons Greyed Out?
Most ribbon icons get greyed out if you enter the ‘Editing Mode’ in Excel. You might have by chance hit the F2 button in your keyboard that triggered this variation. To revert this, you’ll be able to merely choose a unique cell in Excel.
Check out more article on – How-To tutorial and latest highlights on – Technical News