Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Neg Value to Positive (Excel 2003)

    If I copy from say a Bank worksheet, to my Excel worksheet the Credits and Debits...I want to be able to change the Credit column of negative values to positive values in order to SUM the column up. As you are aware when you try to add a column of negatives you get a zero for an answer...I tried copying the column and choosing paste special then VALUES, but they still appear as negatives to the worksheet so still get a zero for an answer. Attached is an example sheet. I cannot add the Credit, nor the Debit column until I can change the entries somehow to see Positive numbers.
    Thanks for any help on this.
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: Neg Value to Positive (Excel 2003)

    > As you are aware when you try to add a column of negatives you get a zero for an answer

    No, adding a column of negative numbers results in a negative number. But the values in your worksheet are not numbers, but text values, since there is a non-breaking space after them.
    Select Edit | Replace.
    Enter Alt+0160 in the Find what box.
    Leave the Replace with box empty.
    Click Replace All.

    The SUM formulas should now return a non-zero result.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Neg Value to Positive (Excel 2003)

    Your problem is not negative numbers, SUM adds those just fine and gives a negative result. Your problem is that those are not numbers, they are text and SUM does not add text very well. They are text because there is a non-breaking space after each of the values. Try running this macro when that sheet is the active sheet:

    <code>
    Public Sub FixData()
    Dim vR As Variant
    ActiveSheet.Cells.Replace Chr(160), "", xlPart
    End Sub
    </code>
    Legare Coleman

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Neg Value to Positive (Excel 2003)

    OK, I had edited that worksheet to much before I sent it to you. I tried the ALT+0160 to replace any breaks but it did not help, so I am likely doing something wrong in the process...I have attached a different sheet without my inerrant edits of previous. The ALT+0160 is not doing it for this one either. With the attached worksheet would I be doing the same procedure as you mentioned previously? As I get no better results. These in this sheet should actually be negative numbers...
    Thanks for the second look.
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Neg Value to Positive (Excel 2003)

    It works for me. Are you typing the 0160 on the numeric keypad (not the number keys above the letters) while holding down the Alt key? My macro also works.
    Legare Coleman

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Neg Value to Positive (Excel 2003)

    No, I was not using the numeric keypad...Thank you...I knew I was doing something in the process wrong. I never stop learning in these applications. I guess thats why I've never gotten bored with them. You guys are great.
    Always Thankful for all your help!!!!
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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