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:

20151203Image01

20151203Image02

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

20151203Image03

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:

20151203Image04

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

20151203Image05

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

20151203Image06

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.

One response to “Changing an imported Excel number field to text in SharePoint

  1. Dean Sutton

    I encountered a similar issue, but solved it in Excel rather than in SharePoint. The advantage is updates can be made in the SharePoint (eliminates the EDIT drawback), but the cost is you need to play with the Excel import file a little each time. (We accomplish this with a recorded macro button now that the process is firm.)

    Assuming AFAI-ID is in Column A looking suspiciously like a number…
    1. Insert a new column in the Excel spreadsheet (example: Insert Column A) to act as a play area.
    [NOTE: format of Column should be “General”]
    2. Use Excel’s TEXT function to convert the numbers to text for the targeted column.
    For example: =TEXT(B2,”000000000000”) where AFAI_ID is column B.
    3. Copy the formula to fill column A
    4. Use COPY – PASTE SPECIAL – VALUES to copy the number as a text to the AFAI_ID column (in our example Column B)
    The cell will add a green triangle to show the numbers have been converted to text.
    5. Lastly, delete Column A (play area is no longer needed)
    6. Import to SharePoint as before and SharePoint now realizes we are have number-text, not numbers.

    Also, if the data was coming from an Access database as indicated in the article, you should also be able to add the text conversion in the MS-Query data source via a combination of the FORMAT and convert-to-string CSTR functions.
    example: AFAI_ID: FORMAT(CSTR(AFAI_ID),”000000000000″)

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s