Oddity with importing SharePoint data to Excel

Sometimes Excel is too helpful

Posted on 13 April 2012 by Eric in the category Office

Tagged under: Excel, List, Import

Imagine a scenario where you have a list of items with a product number or room number in the format of XX-XXXX. Now imaging you have an Excel workbook the pulls in data from this Sharepoint site including that field.

What you might notice is that Excel sees that it resembles a date and formats it as such for you. Handy, but not useful in this scenario. I tried a few solutions I found on Bing, like preformatting the destination field to text or general and putting an apostrophe before the data.

The apostrophe trick did work​ if I was manually inputting the data, but would fail when doing a data refresh.

I eventually found a handy little setting in the Data Connection properties. Go into the data connection and click the Options link,

From there, tick to enable the Disable date recognition option under Other Import Settings.