+ Reply to Thread
Results 1 to 3 of 3

Number Formatting Not Converting into Connection Worksheet

  1. #1
    Registered User
    Join Date
    06-03-2014
    Posts
    2

    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
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13
    Posts
    953

    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
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    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?
    Ron
    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

Bookmarks

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