My SP site's regional settings are set to Germany. In Germany a comma is used as the decimal point instead of a dot. So if I have a calculated column that shows the result of 50/100 it will print 0,5. My calculated column function is more complex then that but I'm simplifying for the sake of this question.

Is there anyway I can override the regional settings to show a dot instead of a comma? So I'd want the output to be 0.5?

  • Please do share your Calculated Formula.. Vieleicht kan ich etwas andern (poor attempt at writing german) Oct 23, 2015 at 18:30
  • @DannyEngelman: "filter: alpha(opacity="&[opacity]&"); opacity: "&[opacity]/100&"; " and [opacity] is a number between 0 and 100. Oct 23, 2015 at 18:47
  • You are creating HTML and use the datatype=Number trick... Oct 23, 2015 at 18:49

1 Answer 1


SharePoint does not have a SUBSTITUTE (of multiple characters) like Excel has.

So you have to use the REPLACE function (which does not have the same syntax as Excel)

=REPLACE( n1 , FIND(",",n1) , 1 , "." )

If you want to do more Replacements you have to nest the statements (and SharePoint has a limitation of 7 nested statements...)

All tested and tried functions that work in SharePoint: https://www.365csi.nl/vm365com/365coach/#/Calculated_Column_Functions_List

  • Thank you! But now I am wondering if different regions use different characters for decimal. I might have to do something else to make sure decimals always use a dot... Oct 23, 2015 at 18:47
  • 1
    commas and periods are the only ones on this planet. So this Formula is save for all... Unless you have users on Delta-Centauri-7 .. legend has it they use 0123456789 as decimal separator (but they do not use a decimal notation.. its called DC-7 for a reason) Oct 23, 2015 at 18:51
  • 1
    PS. If you mark your questions as answered they won't turn up in the un-answered list and you give answers some credit... tnx Oct 23, 2015 at 18:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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