Changing an imported Excel number field to text in SharePoint

On a recent “Open Office Hours” call we do in our SharePoint group, we had a customer with an Excel/SharePoint issue. They had an Excel spreadsheet they needed to copy/paste into a datasheet view several times a day, and one of the fields was a “numeric text” field. In other words, the field should be treated as a text value, but it was basically a 12 character field with all numbers. The problem is that when they would import that field, it would change the value to something like 1543E+12, regardless of whether you defined the SharePoint column as text or numeric.

I’ve looked into this in the past, and all the material I found online said basically the same thing… importing a number column from Excel will always have the field being treated as numeric in the translation, even if you define the column in Excel as text. Normally I’d suggest that they pad the field with some text character to get the translation to work as text, but the group that gives them the data from Access were unwilling to make any changes. Basically, we had to do something on the SharePoint side to help them.

Fortunately, I found a workaround that took care of the condition in this particular situation. It’s not perfect for all cases, but perhaps you’ll find this useful if your situation is similar.

In the SharePoint list, define the incoming field as a number, and make sure it’s set for zero decimal places:

Next, create a calculated field in the list where the text transformation will occur:

In the calculated field, set the formula to append a non-numeric value onto the original imported field. In this example, I’m just using &”” so that it thinks there’s a text character at the end, which then makes the whole field a text value:

Here’s what the view looks like when I use the original number field (note the comma separators):

And here’s what the new calculated text version of the field looks like:

This worked great for my site. The customer tried it out, and they were ecstatic with how much time and effort it saved them.

The only two drawbacks that I can see are:

• Leading zeroes – It would likely trim off leading zeroes during the import, so converting it to a text field would likely do so without those zeroes. In my case, that wasn’t an issue.
• Editing – If you had a reason to edit that text-based value, you’d have to have them edit the numeric field as you can’t edit the calculated text field. That might confuse some people, but again… not an issue in either case here.

Hopefully this technique will be helpful if you find yourself in the same situation. All the Google searching I saw says there’s no way to “fix” an Excel import to treat it properly as a text value coming in. This approach at least gets you almost all of the way there, especially if you have no control over the spreadsheet data you’re receiving.