Gee, you sure think it wouldn’t be too much for Microsoft SharePoint developers to add an option in the Column settings for the Number field to select to not show a comma. This is a complaint I get from users all the time…HIDE THE COMMA!
So one of the workarounds we have to do is to create a second field…a calculated field that removes the comma:
=TEXT([NumberColumnName],”0”)
Then make sure you select the data type as Number for the calculated field.
Keep in mind that if you need to have a decimal place then the above won’t work for you. To retain a decimal place use the following:
=text([NumberColumnName],”0.00”)
Change the number of zero’s behind the decimal to equal how many you allow in your original Numeric Field. With this formula if you entered 23,546.41 the calculated field will display it as 23546.41. If you entered 23,546 the calculated field will show it as 23,546.00 .
Hi,
ReplyDeleteThis is a problem I am having, however I don't understand your syntax. Could you give more details please. I have a list of mobile phone numbers, with PUK codes and SIM numbers... lots of numbers. I save the spreadsheet, then import in WSS. I am guessing that the formula you suggest is entered into the spreadsheet before importing...?
tr0pp0, you have to create a new COLUMN in your SharePoint LIST that the Column Type is a CALCULATED column. Then paste the formula listed above substituting NumberColumnName with the actual name of your SharePoint List Column that contains the number.
ReplyDeleteThis can't be done if you are choosing to Create a List using the Import option. You would have to create this Calculated Column after you import your data then.
This works great for removing the comma but then the number is no longer treated as number. I'm building a pie chart with google charts and xsl, but the comma causes issues. If I use this method to create a calculated column without the comma, then the pie chart treats the numbers as text and not a number so the pieces of the pie are not scaled correctly. Is there any alternate solutions?
ReplyDeleteKevin,
DeleteI know this is old, but for any that turn it up in a search...
> "but then the number is no longer treated as number."
> "building a pie chart ... treats the numbers as text."
Remember the new column is just a "caclulated" version of your original columns which should still be in the list, although likely not displayed. You should be able to use the orginal column that *is* still in number format for the pie chart/any other calculations, while using the calculated column for display purposes. Yes?
Thanks Linda, just used this on SP2007! worked a treat :)
ReplyDeleteWorks great, I added 5 zeros to mask the number, also added text to number. Makes it uniform
ReplyDelete="Number-Num"&TEXT([NumberColumnName],”00000”)
Result field: Number-Num00000
Ran into this dreadful situation with SharePoint 2013 , but also wanted to add to it. You can add another & =""&"[+}"&"" on the end to also open any specific views/pages. I happen to use it to open a tabbed display of the selected item.
ReplyDelete