Issue with number column with comma while publishing spreadsheet list to SharePoint list
Hi,I am wondering if you guys had a solution / issues with xls list publishing to a list.I have a number column in xls list, when i published the list to Sharepoint list its published with number column with a comma.But i do not wanted comma in the number format. So i changed to text with single line in sharepoint list columnsettings, entire data changed with exponenential numbers. However i change the xls column property to text and published its behaving the same number format with comma and when changed the list column to text then the data is changing to exponential data.Is there any solution/help will be appreciated. I want a number without comma format ( since changing to text is not working.)Thanks
December 10th, 2008 1:04am
Hi,
After you have imported the Excel file into a SharePoint list. You can create a calculate column to convert the format to number without comma.
Here is the formula for your reference: =TEXT(Original_Number_Column_Name,"0")
For more information, please refer to: TEXT function (http://office.microsoft.com/en-us/sharepointserver/HA011610831033.aspx)
-lambertPosting is provided "AS IS" with no warranties, and confers no rights.
Free Windows Admin Tool Kit Click here and download it now
December 11th, 2008 11:11am
Hi,
After you have imported the Excel file into a SharePoint list. You can create a calculate column to convert the format to number without comma.
Here is the formula for your reference: =TEXT(Original_Number_Column_Name,"0")
For more information, please refer to: TEXT function (http://office.microsoft.com/en-us/sharepointserver/HA011610831033.aspx)
-lambertPosting is provided "AS IS" with no warranties, and confers no rights.
December 11th, 2008 11:11am
This formula only works if you don't need to retain any decimal places. Is there a formula that you can remove the comma but still retain the decimal places? I have been trying different variations of TEXT and LEN and haven't got it.MSILinda
SharePoint Architect
Free Windows Admin Tool Kit Click here and download it now
January 6th, 2010 9:46pm
This formula only works if you don't need to retain any decimal places. Is there a formula that you can remove the comma but still retain the decimal places? I have been trying different variations of TEXT and LEN and haven't got it.MSILinda
SharePoint Architect
January 6th, 2010 9:46pm
Nevermind, I found the mofified version of that, that will retain the decimal places.=TEXT([NumberFieldName],"0.00"adding how ever many zeros behind the decimal that you need retained.MSILinda
SharePoint Architect
Free Windows Admin Tool Kit Click here and download it now
January 7th, 2010 8:04pm
Nevermind, I found the mofified version of that, that will retain the decimal places.=TEXT([NumberFieldName],"0.00"adding how ever many zeros behind the decimal that you need retained.MSILinda
SharePoint Architect
January 7th, 2010 8:04pm
Hi:
We applied this function on a calculated column. But despite of making output of calculated column as Number; column doesn't retain numeric characteristics and when sort list on this column then rows are sorted in text order e.g. 1, 100, 1000, 2, 3, 4. My basis problem of removing "," and displaying list is numerically sorted remains same. Pls advise if you have any solution.
- Deepak
Free Windows Admin Tool Kit Click here and download it now
January 19th, 2010 1:29pm
The formula is similar to having column properties as "TEXT". The formula is not required for converting a number to Text. Just change your existing column properties will remove your comma in number.
November 11th, 2010 1:06am
Easily the most ridiculous thing to have to deal with. Excel lets me remove the thousands separator with nary an after thought. I can do this in SQL easily enough also - What gives MS? Your vaunted SP2010 can't figure out how to convert and
cast on the back end to make this happen? C'mon guys, I dont want to store integer index numbers with commas in them. Beyond looking stupid they are a waste of space. On a 2,000 record list, its 2,000 extra characters.
Surely there is a way around this without converting down into text formatting. Its insulting.
Free Windows Admin Tool Kit Click here and download it now
June 20th, 2011 7:04pm