Forum Discussion

Sofia-Cyan's avatar
Sofia-Cyan
Copper Contributor
Jan 03, 2019

Figure/Number format to display with comma

Hi Everyone ;-)

 

We work on sharepoint.

Where we set-up financials in excel and word.

These files are then worked on by various groups.

 

Normally we use this format: 

## ##0;(## ##0);"–"

 

But – this specific client wants their figures (numbers) to display with commas.

 

Only problem with that the formula:

EG =SUM(D6:D13) 

It does not work. ;-(

We are then forced to put a (') in front of the number to display as the client wants it too.

 

I have attached a screenshot and an example excel spreadsheet to give an example of what I mean.

 

 

Thanks so much in advance.

BI & Data Analysis
excel
Formulas and Functions
  • IngeborgHawighorst's avatar
    IngeborgHawighorst
    MVP
    Jan 04, 2019

    Hello,

     

    I can't reproduce the problem. Your blue table uses the custom format 

     

    ## ##0;(## ##0);"–"

     

    If I change the custom format to ... 

     

    ##,##0;(##,##0);"–"

     

    ... it still works fine for me.  However, in my regional settings, the comma is the Thousand separator. There are other regions where the Thousand separator is a dot and the comma is the decimal separator. Maybe that is the problem in your case? 

     

    You can quickly test this by entering a number and formatting it with the Currency format. What is used as the Thousand separator? 

    • Sofia-Cyan's avatar
      Sofia-Cyan
      Copper Contributor
      Jan 04, 2019

      In my previous post

      I have taken a screenshot of my PC interface.

       

      Is that the Region settings you are referring to?

      • IngeborgHawighorst's avatar
        IngeborgHawighorst
        MVP
        Jan 04, 2019

        Yes, in a way. The region settings are define at the operating system level, so Windows or Mac, whatever you use. Excel then picks it up from there. I can see that your settings use a decimal comma and a dot as the thousand separator. Since your client wants to use a comma instead, their regional settings are probably different. 

         

        If you work with Excel online, with data saved in SharePoint, these two can also have an impact on the display. I read that the settings of the SharePoint site will determine the settings for the separators and if you want to change that you need to change the region settings for the SharePoint site. 

         

        Is that anything you can approach?

         

         

Resources