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:
When formatted as numbers, the leading zeroes may be stripped, and commas or periods added to differentiate the thousands places:
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.
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.