Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Number format error

    When i copy and paste from a SqlServer program i use at work, into excel, the result in one of the columns is a rounded number 7081113831684220, if i format in text i get the unrecogniozable number format ie: 7.08111E+15...how do i convert this text # to the actual number (0000000007081113831684228) which I see in my SQLserver screen. I hope this makes sense to the experts, i am not a programmer, just an end user.

  2. #2
    WS Lounge VIP Browni's Avatar
    Join Date
    Dec 2009
    Location
    Rochdale, UK
    Posts
    1,652
    Thanks
    38
    Thanked 161 Times in 139 Posts
    Format the cell/column as a number.

    If you need to see the leading zeroes use a custom format of '00000..." to the length of your expected input string.


  3. #3
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Thanks for the trick, Ive tried that, but still get the ending number as a zero when i need the ensing number to be an eight, as it was before i c&p'd into excel

  4. #4
    WS Lounge VIP Browni's Avatar
    Join Date
    Dec 2009
    Location
    Rochdale, UK
    Posts
    1,652
    Thanks
    38
    Thanked 161 Times in 139 Posts
    Sorry, missed the difference in the last character.

    What happens if you copy & paste to a CSV file and open that with notepad for example? Does it show the same difference?

  5. #5
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts
    ahh, thanks! the 8 shows up if i c&p into a text document, but I cant get the text document then into excel with the correct formatted #.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You need to format the cell as text before pasting the value in because Excel only allows 15 significant digits in number values.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Thanks I'll try that!!

Posting Permissions

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