+ Reply to Thread
Results 1 to 3 of 3

Number Formatting Not Converting into Connection Worksheet

  1. #1
    Registered User
    Join Date

    Number Formatting Not Converting into Connection Worksheet

    I have an excel document saved in a SharePoint document library. I then have another excel document where I created a connection back to the document being saved on SP (went to data connections->add->browse for more->enter the SP URL & found the document) and then opened that connection up as its own worksheet (existing connections->chose my connection). In the original worksheet (saved on SP), there are about 20 columns that are formatted as numbers. However, when I open the connection up in the new document, half of these columns are still number formatted, but the rest are being displayed as text EVEN THOUGH they are technically formatted as numbers (I right click->format cells & they are formatted as numbers). I can click into a cell and press enter and it will "come up to speed" and enter into number formatting, but as soon as I refresh my connection back to the original document, they go back to their text formatting despite the fact that the connection they are drawing from has them saved as numbers! Very frustrating. Its not a SP issue because I recreated the scenario by saving the original document to my computer and it did the same thing. I even went through one column in the original document and made sure there were no spaces saved to trigger the column to go to text- nothing. Any suggestions?

  2. #2
    Forum Contributor noboffinme's Avatar
    Join Date
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13

    Re: Number Formatting Not Converting into Connection Worksheet

    Just an idea, create a column that multiplies the 'numbers' by 1 therefore forcing it to become a number.
    Remember you are unique, like everyone else

  3. #3
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365

    Re: Number Formatting Not Converting into Connection Worksheet

    If the original values are text and you simply change the format of the cells to a number format...that does NOT impact the original values. They will still be text.

    You may need to actually effect the conversion:
    • Select a blank cell
    • CTRL+C ... to copy the blank cell
    • Select the range of "numbers"
    • Home.Paste.Paste_Special
    ...Check: Add
    ...Click: Finish

    Does that help?
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 05-15-2015, 04:12 PM
  2. Formatting not updating upon data connection refresh
    By jmewebb in forum Excel General
    Replies: 1
    Last Post: 06-03-2014, 02:05 PM
  3. [SOLVED] Re: macro for converting number stored as "text" (or preceeded with ') to "number" formatting
    By markx in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-30-2006, 07:14 AM
  4. Replies: 1
    Last Post: 06-23-2006, 10:20 AM
  5. Converting number to text with formatting in VBA
    By Rowan Drummond in forum Excel General
    Replies: 3
    Last Post: 12-18-2005, 06:10 PM


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1