Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Sep 2001
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pasted Column Won't Sum (2002 SP2)

    I'm an Excel newbie (pretty much), so please forgive me if I'm missing something obvious.

    I copied and pasted into Excel a 4 column table showing my order history at an online CD store.

    One of the columns is price of each order, which I wanted to total at the bottom

    If I try to autosum the column of figures in the next available cell at the bottom, I get a zero.

    If I just try a simple formula = to D5+D6, I get a #VALUE error message.

    MS Help on this error message is all greek to me, but like I say, I'm a newbie.

    The column of prices are formatted as general numbers. They have a $ in front of each, but are not formatted as currency. I tried find and replace to remove all $'s. I tried entering the values of D5 + D6 manually elsewhere in the sheet, and summed that - worked fine. I copied the formatting of those 2 good cells to the original D5 + D6, which among other things, removed "wrap text." But it still won't sum correctly.

    I could continue struggling with this, or I could ask the enlightened Excel elite that prowl around here. Guess which I'm doing <g>.

    Many TIA.

    Paul

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pasted Column Won't Sum (2002 SP2)

    Often, when you paste numbers from another source (such as a web page) into an Excel worksheet, they come in as text - the fact that they have a dollar sign, but are formatted as General Number is a clue for this. Often, it can be remedied as follows:
    - Select a single column of numbers-as-text
    - Select Data | Text to Columns...
    - Make sure that Delimited is selected
    - Click Finish
    Post back it that doesn't help.

  3. #3
    Star Lounger
    Join Date
    Sep 2001
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasted Column Won't Sum (2002 SP2)

    Hans

    Thanks for your suggestion. I followed your steps, but it did not work.

    Could I have done something wrong? There were "next" steps before the "finished" step - what do I choose there?

    Any other ideas?

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pasted Column Won't Sum (2002 SP2)

    Usually, it is not necessary to enter anything in the intermediary steps.

    Can you post a small workbook that demonstrates the problem?

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts

    Re: Pasted Column Won't Sum (2002 SP2)

    Have you tried entering a 1 in a spare cell (delete later).
    Copy this cell with the 1 in it, then select the column of offending numbers and use
    Edit-Paste Special-Multiply.
    Format the cells afterward as currency if required.

    zeddy

  6. #6
    Star Lounger
    Join Date
    Sep 2001
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasted Column Won't Sum (2002 SP2)

    zeddy:

    thanks for this. Pasting changed the format enough to make the numbers align/justify left, but the calculations are still wrong.

    Per Hans' suggestion, I have attached a simple example workbook. The first column contains the offending numbers, and the total at C13 is an attempt to sum the entire column. The next column over, I tried to simply add D5+D6. Different errors, but both errors nonetheless.

    Many thanks for your efforts to help!

    Paul

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pasted Column Won't Sum (2002 SP2)

    Your numbers have one of the sticky-spaces after them (ASCII 160)
    Select all (ctrl-a)
    edit replace (ctrl-h)
    Find what: hold alt key and enter 0160 from numeric keypad.
    leave replace with blank
    <replace all><ok>

    Steve

  8. #8
    Star Lounger
    Join Date
    Sep 2001
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasted Column Won't Sum (2002 SP2)

    Steve:

    Your solution certainly did the trick.

    Now, what is the secret to how you knew that was the problem, i.e. where did you look?

    Thanks

    Paul

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pasted Column Won't Sum (2002 SP2)

    The #value said that it was text
    I looked at one the values and it looked like a space at the end.
    I did a find/replace to get rid of spaces, but it did NOT go away. I edited everything out of the cell but the last "space" and then used the CODE function to tell me its ASCII code (I got 160). So then I knew to find/replace the <alt>0160 character.

    Steve

Posting Permissions

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