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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics