Ensuring a - symbol converts to a 0

[Grover]

Driving me insane right now. I have multiple users on the same spreadsheet. When a value is zero some enter 0 and some enter -
Is it possible to format the cell so if someone types - it automatically changes to a zero

Thanks

 

[levy77]

Hi Grover

Have you considered just using a find and replace?
You could also set up a shortcut using a simple macro.

 

[Grover]

I havent. Any guidance on how to do this would be fantastic. I do run a VBA that automatically capitalises in one column as the people using the spreadsheet seem incapable of a consistant format

 

[Grover]

I think Macro would be the way to go if you have any guidance. Many thanks

 

[Grover]

Or is there a way to format the cells

 

[levy77]

A very basic macro to replace - with 0 is

Sub DashtoZero()
'
' DashtoZero Macro
' Convert - to 0
'
' Keyboard Shortcut: Ctrl+y
'
Cells.Replace What:="-", Replacement:="0", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False
End Sub

You can also find under Edit>Find>Replace.

 

[Grover]

So i go into vba right? Class module? and paste code? Thanks

 

[Grover]

Hi. Got the macro to run but is there a way of this happening automatically rather than having to run the macro. Alos i only want it to apply to one sheet of the workbook. Thanks

 

[DSCfromCFA]

G'day Grover,

Depending on the nature of the data being entered, another method is to use Data Validation (found in the Data ribbon).

Select the column(s) you want to restrict the use of the "-" Select data Validation from the ribbon and again from the drop down, then select Whole number OR Decimal, then either select "greater than or equal to" and then put 0 in the Minimum box.

Select the Error Alert tab and enter a simple message such as "- not allowed, enter 0 instead".

Note- if you use negative numbers you will need to use the "between" option instead of "greater than or equal to 0" and apply limits that are outside of any possible range you would expect. This allows a "-" to be used with a number, but not by itself.

Cheers

shane

 

[Grover]

Awesome. Works great and ensures they have to enter 0. Thanks. All set up and good to go