Ignore maths symbols

**marcushere** · 03-15-2012, 01:35 PM

Hi,

I'm using Excel 2003. I'm not very experienced. My spreadsheet has some columns that get averaged automatically. I want the users of the spreadsheet to be able to enter a dash ( the minus sign ) if there is no data for the cell. At the moment this causes excel to think that a minus number is coming. Is there any way of getting excel to just display the dash without doing anything to it ? ( They will never be entering negative values into the cell )

Many Thanks in advance.

Marcus

**shg** · 03-15-2012, 01:37 PM

If there is no data, have them leave the cell blank.

If you want something affirmative, have them enter "no data" or something similar.

**marcushere** · 03-18-2012, 01:49 PM

Thanks very much for the reply. - However ...
The people who will use the spreadsheet are older people who are not comfortable with computers. They are used to using a paper form where they enter a dash if there is no data ( to show that it hasnt been lost or forgotten but really isnt there ). So I've made this spreadsheet absolutely identical to the paper one so that they need zero training to use it and to make the transition as smooth as possible for them. That's why I'd ideally like to trap the keystrokes and change the behaviour of the minus sign in that way. I understand if it's a limitation of excel though. I might have to write it as a web page with javascript,php and mysql if excel can't do it. What do you think ?

**shg** · 03-18-2012, 01:54 PM

I can enter a just a hyphen in a cell in Excel 2003 (and 2007 and 2010) with no problem. What happens when you do?

**marcushere** · 03-20-2012, 04:15 PM

As soon as I enter the '-' sign the cell address appears and the border of the cell starts shimmering . If I press the enter key a dialog box appears telling me there is an error in the formula. If, instead of hitting ENTER , I press the arrow key to get out of the cell, it thinks I'm selecting a range of cells for the formula.

**marcushere** · 03-21-2012, 10:17 AM

It looks like I'm going to have to use a macro to trap keystrokes.

**Andy Pope** · 03-21-2012, 10:41 AM

If I enter - and press either ENTER or TAB then the dash is entered without issue.

If I enter - and then use the arrow key I get 'shimmering cell' and cell reference because excel is now expecting a cell reference.
You will not be able to capture key stroke as VBA will not run whilst you are in edit mode.

**Spectre** · 04-13-2012, 03:20 PM

In order for the hyphen to appear without Excel thinking it's part of an equation, you have to put an apostrophe in front of it. The apostrophe will not show up in the cell, but it MUST be entered before the hyphen every time. Would they be able to use a different symbol or character instead? I wouldn't think that would confuse them too much.