I have an Excel spreadsheet with some sort of generated data. This spreadsheet is then presented to a user who is supposed to fill in some numbers. Then there is a column with a simple sum of user inserted data.

The problem is that eventhough all of my users are in central Europe which uses comma(",") as a decimal separator, some of them still use dot(".") as a decimal separator. Excel then treats this cell as a datetime and there's no way user can switch cell format back to number (spreadsheet is password protected and most of cells are locked).

My question is: Is there a way to treat both dot and comma as a decimal separator? I can't use VBA and the column with a sum of user typed values has to be there (eg. converting cells to text won't help me, I guess, because sum does not work on text cells).

  • This spreadsheet is then automatically processed so I need to be sure that noone adds additional columns/cells and noone edits those already present (except the ones I need). I thought this can be done without using VBa and VBa and macros can be restricted by computer/domain admins – MartinM Oct 4 '16 at 8:58
  • Pls provide some sample data to illustrate the problem – thilina R Oct 4 '16 at 16:31
  • If you cannot educate your users regarding proper data entry, I would suggest you structure your sheet so as to only allow data entry via a User Form. You can then have separate boxes for the integer and decimal parts. – Ron Rosenfeld Oct 4 '16 at 21:11

Let each user set their preferred decimal separator for Excel using File > Options > Advanced

enter image description here

Untick "Use system separator" and set the desired separator symbols. This way User A can use a decimal point with a number, and User B, who prefers decimal comma, can open the same file and will see the same numbers with decimal commas.

Excel is international and can cater to these need. It's just a tick in a box. No VBA required.

  • 1
    thanks I'm aware of this option, but it presents another problem for user who is sometimes a person which is not so skilled with PC, so I try to make their life easier (making mine harder obviously :) ) – MartinM Oct 4 '16 at 9:03
  • 1
    You can use teylyn's method in a macro with a button. In this way the user will only press the button to use the right decimal separator. – maudam Oct 4 '16 at 9:18
  • 1
    Well, does that user also use the wrong decimal symbol when he uses internet banking? At some point in time, there will need to be some user education. Why can people happily use online shopping and eBay, but as soon as they enter the office in the morning they seem to lose all computer literacy and need to be spoon fed the simplest solutions. – teylyn Oct 4 '16 at 9:25
  • @teylyn you're absolutely right, but try to tell this to your employer. – MartinM Oct 4 '16 at 11:22
  • @MartinM You mean your employer is happy with the overhead cost that untrained employees create? – teylyn Oct 4 '16 at 20:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.