SharePoint Import from Spreadsheet Option Data Type Problem

SharePoint offers an “Import from Spreadsheet” option to create a custom list. The user names the list, identifies the range of cells required, and SharePoint imports the data, using the top row entries as the column names.

While most of the time this process is seamless, an issue may arrive when SharePoint chooses the data type for each of the columns. Any column containing only numbers will be given a data type of number, which may not be the desired result, particularly when number formatting is applied. For example, a column containing invoice numbers should be formatted as text:

00986678

12939933

49943929

When formatted as numbers, the leading zeroes may be stripped, and commas or periods added to differentiate the thousands places:

98,6678

1,2939,933

49,943,929

To prevent this incorrect data type assignment, temporarily add an alpha character to the first entry in any column where the data contains only numbers, but where the desired data type is Text.

B00986678

12939933

49943929

SharePoint will conclude that any column containing even one alpha character should be imported as type Text. You may then remove the temporary alpha character to restore the first record to its original data.