How to format numbers with commas in SharePoint Dataview (using decimal-format functionality)

In a dashboard project we had to display certain valuta fields that came from a SQL-SERVER table.Those fields had following formatting:
139367709,8800
0,00000,00
51345998,3700

The goal was to display them as European style valuta:
139.367.709,88
0,00
51.345.998,37

To format those numbers to numbers with commas in SharePoint Dataview I used the xslt format-number() function

<xsl:value-of select="format-number(@YourField, "$#,##0.00;-$#,##0.00")"/>

Result was empty fields and a sad look on my face.
So I asked the help of a developer (since I am not one, but I like to mess around in code).
Apparently, the commas in the numbers were giving the format-number() a hard time to recognize it.
After some playing around with the string() and number() functions, I was no step closer.

What I thought would help me, was to replace the commas with a point. I tried the w3schools xslt replace() function, but that gave me an error in SPD, saying the function was not recognized, although it appears to be a valid xslt function.

The trick here is to use the translate() functionality, which is essentialy the same as replace(), but it converts the string instead of replacing it.

<xsl:value-of select='format-number(translate(@YourField,",","."), "#.##0,00")'/>

Now we had values instead of blanks, but the formatting still wasn’t right.
Switching the point and comma in the format-number gave errors as well. Then our eyes fell on the optional decimalformat parameter.After a quick google for a working example we learned that we had to add a separate xsl command to describe how we wanted our field to look like:

<xsl:decimal-format name="european" decimal-separator=',' grouping-separator='.' />

Note: the description of the decimal-format has to be placed outside the xsl:template your field is in because it is not a valid line that you can add in a xsl:template.. More about the decimal-format here.

The final xsl line looked like this and gave us the correct result:

<xsl:value-of select='format-number(translate(@YourField,",","."), "#.##0,00", "european")'/>

Long story short
If you want to translate numbers, use the default SPD “Format item as – number” functionality or build-in Xpath Expressionbuilder which will get you something like following code:

<xsl:value-of select="format-number(@YourField, "$#,##0.00;-$#,##0.00")"/>

If you want to display it in a different way, use the decimal-format functionality which will get you something like this:Outside the xsl:template block:

<xsl:decimal-format name="european" decimal-separator=',' grouping-separator='.' />

To display the field:

<xsl:value-of select='format-number(translate(@YourField,",","."), "#.##0,00", "european")'/>

About: Marijn

Marijn Somers (MVP) has over 14 years experience in the SharePoint world, starting out with SP2007. Over the years the focus has grown to Office 365, with a focus on collaboration and document management. He is a business consultant at Balestra and Principal Content Provider for "Mijn 365 Coach" that offers dutch employee video training. His main work tracks are around user adoption, training and coaching and governance. He is also not afraid to dig deeper in the technicalities with PowerShell, adaptive cards or custom formatting in lists and libraries. You can listen to him on the biweekly "Office 365 Distilled" podcast.


One thought on “How to format numbers with commas in SharePoint Dataview (using decimal-format functionality)”

  1. My friend, after 4 hours of pain I found your article and solved my problem. Thanks a lot.

Leave a Reply

Your email address will not be published. Required fields are marked *