Home

Figure/Number format to display with comma

%3CLINGO-SUB%20id%3D%22lingo-sub-308706%22%20slang%3D%22en-US%22%3EFigure%2FNumber%20format%20to%20display%20with%20comma%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-308706%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Everyone%20%3B-)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20work%20on%20sharepoint.%3C%2FP%3E%3CP%3EWhere%20we%20set-up%20financials%26nbsp%3Bin%20excel%20and%20word.%3C%2FP%3E%3CP%3EThese%26nbsp%3Bfiles%20are%20then%20worked%20on%20by%20various%20groups.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3ENormally%20we%20use%20this%20format%3A%26nbsp%3B%3C%2FEM%3E%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%23%23%20%23%230%3B(%23%23%20%23%230)%3B%22%E2%80%93%22%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3EBut%20%E2%80%93%20this%20specific%20client%20wants%20their%26nbsp%3Bfigures%20(numbers)%20to%20display%20with%20commas.%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3EOnly%20problem%20with%20that%20the%26nbsp%3Bformula%3A%3C%2FP%3E%3CP%20class%3D%22p1%22%3EEG%20%3DSUM(D6%3AD13)%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3EIt%20does%20not%20work.%26nbsp%3B%3B-(%3C%2FP%3E%3CP%20class%3D%22p1%22%3EWe%20are%20then%20forced%20to%20put%20a%20%3CSTRONG%3E(')%3C%2FSTRONG%3E%20in%20front%20of%20the%20number%20to%20display%20as%20the%20client%20wants%20it%20too.%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3EI%20have%20attached%20a%20screenshot%20and%20an%20example%20excel%20spreadsheet%20to%20give%20an%20example%20of%20what%20I%20mean.%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F64629i692050C1ADD306AB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screen%20Shot%202019-01-04%20at%201.16.34%20AM.png%22%20title%3D%22Screen%20Shot%202019-01-04%20at%201.16.34%20AM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3EThanks%20so%20much%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-308706%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-308761%22%20slang%3D%22en-US%22%3ERe%3A%20Figure%2FNumber%20format%20to%20display%20with%20comma%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-308761%22%20slang%3D%22en-US%22%3E%3CP%3EThank-you%20very%20much%20for%20your%20help%20Ingeborg.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20address%20this%20with%20our%20Manager%20today%20and%20see%20what%20she%20says.%3C%2FP%3E%3CP%3EI%20was%20hoping%20there%26nbsp%3Bwould%20be%20another%20alternative%2C%20as%20that%20sounds%20like%20changing%20the%20region%20settings%20would%20effect%20all%20the%20other%20jobs%20on%20sharepoint.%20Which%20are%20fine%20currently.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-308760%22%20slang%3D%22en-US%22%3ERe%3A%20Figure%2FNumber%20format%20to%20display%20with%20comma%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-308760%22%20slang%3D%22en-US%22%3E%3CP%3EYes%2C%20in%20a%20way.%20The%20region%20settings%20are%20define%20at%20the%20operating%20system%20level%2C%20so%20Windows%20or%20Mac%2C%20whatever%20you%20use.%20Excel%20then%20picks%20it%20up%20from%20there.%20I%20can%20see%20that%20your%20settings%20use%20a%20decimal%20comma%20and%20a%20dot%20as%20the%20thousand%20separator.%20Since%20your%20client%20wants%20to%20use%20a%20comma%20instead%2C%20their%20regional%20settings%20are%20probably%20different.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20work%20with%20Excel%20online%2C%20with%20data%20saved%20in%20SharePoint%2C%26nbsp%3Bthese%20two%20can%20also%20have%20an%20impact%20on%20the%20display.%26nbsp%3BI%20read%20that%20the%20settings%20of%20the%20SharePoint%20site%20will%20determine%20the%20settings%20for%20the%20separators%20and%20if%20you%20want%20to%20change%20that%20you%20need%20to%20change%20the%20region%20settings%20for%20the%20SharePoint%20site.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIs%20that%20anything%20you%20can%20approach%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-308754%22%20slang%3D%22en-US%22%3ERe%3A%20Figure%2FNumber%20format%20to%20display%20with%20comma%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-308754%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20my%20previous%20post%3C%2FP%3E%3CP%3EI%20have%20taken%20a%20screenshot%20of%20my%20PC%20interface.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20that%20the%20Region%20settings%20you%20are%20referring%20to%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-308753%22%20slang%3D%22en-US%22%3ERe%3A%20Figure%2FNumber%20format%20to%20display%20with%20comma%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-308753%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F64647iF6B359A0E0C77F14%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screen%20Shot%202019-01-04%20at%203.57.59%20AM.png%22%20title%3D%22Screen%20Shot%202019-01-04%20at%203.57.59%20AM.png%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EScreenshot%20of%20PC%20interface%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-308745%22%20slang%3D%22en-US%22%3ERe%3A%20Figure%2FNumber%20format%20to%20display%20with%20comma%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-308745%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20can't%20reproduce%20the%20problem.%20Your%20blue%20table%20uses%20the%20custom%20format%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%23%23%20%23%230%3B(%23%23%20%23%230)%3B%22%E2%80%93%22%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20I%20change%20the%20custom%20format%20to%20...%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%23%23%2C%23%230%3B(%23%23%2C%23%230)%3B%22%E2%80%93%22%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E...%20it%20still%20works%20fine%20for%20me.%26nbsp%3B%20However%2C%20in%20my%20regional%20settings%2C%20the%20comma%20is%20the%20Thousand%20separator.%20There%20are%20other%20regions%20where%20the%20Thousand%20separator%20is%20a%20dot%20and%20the%20comma%20is%20the%20decimal%20separator.%20Maybe%20that%20is%20the%20problem%20in%20your%20case%3F%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20quickly%20test%20this%20by%20entering%20a%20number%20and%20formatting%20it%20with%20the%20Currency%20format.%20What%20is%20used%20as%20the%20Thousand%20separator%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Sofia-Cyan
Occasional Contributor

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.

Screen Shot 2019-01-04 at 1.16.34 AM.png

 

 

Thanks so much in advance.

5 Replies

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? 

Screen Shot 2019-01-04 at 3.57.59 AM.pngScreenshot of PC interface

In my previous post

I have taken a screenshot of my PC interface.

 

Is that the Region settings you are referring to?

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?

 

 

Thank-you very much for your help Ingeborg.

 

I will address this with our Manager today and see what she says.

I was hoping there would be another alternative, as that sounds like changing the region settings would effect all the other jobs on sharepoint. Which are fine currently.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies