Forum Discussion
How to disable Excel function triggered by a leading hyphen
I frequently need to place data into excel cells that have leading hyphen.
An example would be copy and paste a list
my animals
- a dog
- a cat
from a word document into excel.
Doing this however generates a formula for the third cell that I do not want.
Can I switch off this feature. To me it is an irksome problem.
- Thank you Peter and Riny,
I now appreciate that when a new sheet is created it has the NUMBER attribute GENERAL which caters for formulae and number entries.
From now on when I create a new sheet intended chiefly for text entry, I will select the whole sheet - click on top left bottom of the displayed sheet, then from the home ribbon in the Number group select TEXT. If subsequently within this sheet I have cells, column or row dealing with numbers, dates etc. then I with use the NUMBER group appropriately for these.
This fully satisfies my original question of how to disable the interpretation of the hyphen as a formula. The general Microsoft help line was unable to directly resolve the issue for me, however I give full marks to the Excel Technical Forum to which it referred me.
6 Replies
Most of my work done when I am using Excel involves text. So I’m thrown off guard when a leading hyphen is interpreted as a formula generator.
The reverse change button also fails to get out of this situation, ESC can help.
Sometimes the response is for Excel to generate #NAME in the cell with a folded top left corner.
Other times the cell identification such as B13 is replaced in the cell or next cell clicked into.
I have managed to get a message in the process :
______________
Microsoft Excel
There’s a problem with this formula.
Not trying to type a formula?
When the first character is an equal (“-”) or minus sign, Excel thinks it’s a formula.
To get around this, type an apostrophe (‘) first.
Apparently Microsoft is aware of the problem it has imposed on its users of text and is not ready to budge from insisting that they get around it by using an apostrophe.
Has consideration been given to being able to classify sheets or cells as non-formula?
This problem has been around for decades however I still get caught out by it every now and then, and it breaks my concentration and angers me.
"Apparently Microsoft is aware of the problem it has imposed on its users of text and is not ready to budge from insisting that they get around it by using an apostrophe.
Has consideration been given to being able to classify sheets or cells as non-formula?"
Probably not. What is a problem to you is a 'feature' that is used day in day out by other Excel users, and Microsoft is unlikely to break backward compatibility to address this issue. Riny_van_Eekelen 's suggestion of formatting cells as text is the solution, the catch being when someone tries to add a formula (starting with "=") to your workbook it will just sit there looking at them as dumb text.
Excel is a calculation tool and, as such, is desperate to convert anything that could be a number or formula into one (excuse my personification of Excel).
PeterBartholomew1 Yeah sorry I'm a daily Excel user/Accountant/numbers/formulas guy and have never seen any benefit to this "feature" whatsoever... but maybe I'm completely missing something.
Horatio_D What you call a "leading hyphen" is interpreted by Excel as a minus sign. It expects that you want to start entering a formula like -5+3 to return a result of 2.
You say that most you your work in Excel involves text. Then, to overcome the problem described, format the columns/cells you intend to enter data in as Text (on the Home ribbon in the Number group), before you start entering data.
In the picture above, I entered - asd in both cells. The top one formatted as General (the default setting) resulting in a NAME error as Excel "translated" the entry to a formula referencing a non-existent named range (=- asd). Hence the NAME error. And as you have noticed, if you enter, for example - B13 (since B13 being a valid cell reference) it returns whatever is in cell B13. If it is empty you'll get zero as a result.
The second cell was formatted as Text, before entering - asd and Excel interpreted the minus sign as a hyphen as part of a text.
Could you please be more complete here in describing what you are doing and what happens.
IN particular, you say that copying and pasting that example list generates, in the third cell, a formula. That is strange indeed. But you don't say what the formula is. That would be helpful. But also:
- is it always the third cell, or every third cell (with a longer list)?
- what, in fact, is the whole task here? Why Excel--if it's words, not numbers--are you creating a database of some kind? Are there multiple columns?
- is the leading hyphen serving some purpose? Could it be eliminated?
Those are just questions I'd be asking if we were meeting in person. It could go on, but the main point is, you make it easier for us to help you if you can give a more thorough explanation of the big picture here.
