Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Apr 2003
    Location
    Carson, California, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hidden formatting? (Excel 2000 SP-3)

    Attached sheet contains data pulled from an Access query linked to an SAP database. After copying and pasting result into Excel the numbers aren't acting like numbers. Can't select and see the Sum=xxxxx in the lower right hand corner of the Excel window or AutoSum. I suspect it is a problem with hidden formatting but copy/paste special (paste values) doesn't seem to clear. Anyone know how to fix?

  2. #2
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hidden formatting? (Excel 2000 SP-3)

    The numbers look like text. If you edit a cell (F2), do nothing just hit enter it turns into a number. You might want to use the VALUE() function in an adjacent to turn the whole column into numbers.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  3. #3
    New Lounger
    Join Date
    Apr 2003
    Location
    Carson, California, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hidden formatting? (Excel 2000 SP-3)

    Thanks! That did it and the addition of the VALUE function will take care of the much larger spreadsheet it came from.

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 5 Times in 5 Posts

    Re: Hidden formatting? (Excel 2000 SP-3)

    A common problem, you'll find over a dozen old threads on this. Four other methods are:

    Save the workbook as HTML and close it, reopen the HTM version and resave in Workbook format.

    For a single column at a time, use the Data | Text To Columns | Fixed Width | Finish.

    For multiple columns, enter the number 1 in any cell, copy it, select the area(s) of wonky data, Edit | Paste Special | Values & Multiply, OK. Delete the cell containing 1.

    Use VBA code such as <!post=this,291530>this<!/post>.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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