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
Highlighted
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
Highlighted

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? 

Highlighted

Screenshot of PC interfaceScreenshot of PC interface

Highlighted

In my previous post

I have taken a screenshot of my PC interface.

 

Is that the Region settings you are referring to?

Highlighted

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.