Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Negative to positive numbers (XP)

    Uploaded data from a remote site to my Excel workbook shows lengthy columns of data as negative numbers - is there a way to change ALL of them in one swoop to display with a + in front of the number in lieu of a -? These numbers will NOT be used in formulae.

    Thanks again

    Aunt Linda

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

    Re: Negative to positive numbers (XP)

    Enter -1 in an empty cell.
    Select this cell.
    Copy it to the clipboard (Ctrl+C)
    Select the cells with the negative numbers.
    Select Edit | Paste Special...
    Click the Multiply option, then OK.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Negative to positive numbers (XP)

    Have you tried a find (-) and replace (+). Use the Edit Replace command.
    Regards,
    Rudi

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative to positive numbers (XP)

    Neither your suggestion nor Hans's works exactly - they both remove the - but neither adds the + symbol.

    Any other ideas?

    Aunt Linda

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative to positive numbers (XP)

    Yes - I did reply to myself. That's why I need help so badly!

    Neither suggestion did the whole trick - each removed the minus symbol - but neither added the plus symbol to the number in the cell.

    Any other ideas?

    Thanks again!

    Aunt Linda

  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

    Re: Negative to positive numbers (XP)

    Select all the relevant cells, choose Format Cells, and on the Number tab select Custom and enter <code>+0;+0</code> as the format. Note: this will not change the values, merely the appearance.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative to positive numbers (XP)

    Perfect! My eternal gratitude.

    Aunt Linda

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative to positive numbers (XP)

    replace with a '+ instead of just a + will do what you want

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Negative to positive numbers (XP)

    Just out of interest....Why do you need a plus sign in front of the number? Is it just for visual sake, as the number is positive by default if no negative sign is present?
    Regards,
    Rudi

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative to positive numbers (XP)

    I teach a beginning Excel class to adults through our local Adult Education school. One of my students asked me to help her with this problem. She speaks very broken English so I pretty much had to guess that this was what she meant. The gist of what she told me (I think) is that when she worked in Mexico "many years ago" she must have worked for a place that did something with oceanographic data they got from somewhere outside their own company. When they imported this data into Excel, she said the had a macro to change all the positive numbers to negative numbers to reflect the depth of the seabed - I think. Bottom line - she couldn't remember the macro they used to convert any negative numbers to a number that displayed a plus sign - so that's what I asked for.

    If it isn't what she wanted, I'll try again.

    Thanks again for all your help.

    Peace

    Aunt Linda

Posting Permissions

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