Workaround: Format SharePoint number column to remove commas

Let’s say you have a column named “TicketNumber” of number type in your list. By default SharePoint formats it to show with commas. If your requirement is not to show those comma’s then here is a workaround.

Create another column of  ‘Calculated’ column type and set the formula as follows:

=TEXT(Original_Number_Column_Name,”0″)

Ex: =TEXT([TicketNumber],”0″)

And then use the calculated column in place of your original column for display purposes.

16 Responses

  1. a href=”” title=””>

          

  2. […] thought this article was a brilliant workaround and wanted to share. It was original posted over at:https://sharenotes.wordpress.com/2010/05/11/workaround-format-sharepoint-number-column-to-remove-comm…Let’s say you have a column named “TicketNumber” of number type in your list. By […]

  3. This trick saved me time.

  4. I get a syntax error. Am I supposed to replace Original_Number_Column_Name with something specific, like a number and a column name?

    • Ok, (duh) my numeric column is Year, so I changed the formula to:

      =TEXT([Year],”0″)

      but I still get this when I hit OK:

      “The formula contains a syntax error or is not supported.”

      I’m using SharePoint 2007.

      • Matt – When you copied the following text ‘=TEXT([Year],”0″)’ the double quotes were formated to a different symbol that is why you are getting syntax error.

        Try this : =TEXT([Year],”0″)

  5. This seems like a pointless workaround. I have a list of building numbers that I’d like to be able to sort as if they were a number (1,2,10, etc.) instead of as a text field (1, 10, 2, etc.) but I don’t want a comma in it. This effectively converts the number column into a text field causing it to be sorted as such (even if I choose number as the type of calculated field). This seems pointless because you can just make the type of column a text field anyway. If there is a way to preserve the sorting and remove the commas that’d be very useful.

    • Steven – I faced the same issue – but able to figure out using SharePoint designer – Find my steps below:

      1. I did the above work around to remove commas from a number column
      2. Opened the sharepoint list page in SharePoint Designer and converted the list into XSLT Data View
      3. Click the column which you want to sort in designer (split mode) which will directly take you to the xsl code delaration of column properties as shown below, Minutes is the column which I enabled with above workaround and enabled sorting by changing the fieldtype from x:string to x:int.

      @Minutes Minutes
      Minutes
      1
      x:int

      This works for me.

      • Steven – I faced the same issue – but able to figure out using SharePoint designer – Find my steps below:

        1. I did the above work around to remove commas from a number column
        2. Opened the sharepoint list page in SharePoint Designer and converted the list into XSLT Data View
        3. Click the column which you want to sort in designer (split mode) which will directly take you to the xsl code delaration of column properties as shown below, Minutes is the column which I enabled with above workaround and enabled sorting by changing the fieldtype from x:string to x:int.

        xsl:with-param name=”fieldname” @Minutes /xsl:with-param
        xsl:with-param name=”fieldtitle” Minutes /xsl:with-param
        xsl:with-param name=”displayname” Minutes /xsl:with-param
        xsl:with-param name=”sortable” 1 /xsl:with-param
        xsl:with-param name=”fieldtype” x:int /xsl:with-param

        This works for me.

  6. That didn’t work either. The view now has problems grouping things and still sorts it as a string.

  7. If the field is blank, the calculated column will show “0” with the above solution. For the calculated column to also be blank, use
    =TEXT(Original_Number_Column_Name,”#″)

  8. Use the following formula in a calculated field:

    =FIXED([],0,TRUE)

    This will keep the field as a numeric value without commas and will sort in number order.

  9. Use the following formula in a calculated field:

    =FIXED([Your_Number_Field_Here],0,TRUE)

    This will keep the field as a numeric value without commas and will sort in number order.

    • I cannot seem to get this to work. I have a field (ProjectCode) that pulls data from an external source. It is a text field. So, I create a calculated column setting ProjectCode to a datatype of Number (ProjectNumeric). It now sorts great, but the comma appears. I then try another calculated column =FIXED([ProjectNumeric],0,TRUE). Great that the commas are gone, but the sorting is not correct. On this second calculated column I tried setting the data type returned as both single line of text and number…neither works. Any suggestions? Thank you!

  10. It’s an remarkable post designed for all the internet viewers; they will obtain benefit from it I am sure.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: