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:




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.

About Joy Lavigne (Adkins)

SharePoint and O365 Business Analyst for a mid-sized organization. Recovering SharePoint on-prem Administrator. Frequent speaker at SharePoint Saturday events. Teller of terribly corny jokes. View all posts by Joy Lavigne (Adkins)

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: