Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Trailing negative sign (2000 SR1)

    I do a lot of work with AS400 data, often copying data from my Client Access window and pasting into Excel. This works great with one exception -- negative values are noted with a trailing negative sign, which then causes Excel to treat the pasted data as a text value "100.00-" instead of the correct numeric value "-100.00". Is there any way to train Excel to interpret this information correct? For now I use an If to test for the trailing negative and if necessary translate using =IF(RIGHT(H2,1)="-",-VALUE(LEFT(H2,LEN(H2)-1)),H2) and copying down.

    For large operations I use ODBC connections or AS400 queries or some other data transfer method as the situation requires, and they all handle the negative values properly, but the copy/paste works best for a lot of limited sets of data.

  2. #2
    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: Trailing negative sign (2000 SR1)

    I think there is no way to train. Either the formula as you are doing or a custom routine (in personal.xls) maybe linked to a button if you do it frequently which essentially would do the same thing only loop thru each cell in the selection

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trailing negative sign (2000 SR1)

    =IF(ISNUMBER(H2),H2,-SUBSTITUTE(H2,"-",""))

    would operate faster.
    Microsoft MVP - Excel

  4. #4
    New Lounger
    Join Date
    Jun 2003
    Location
    Australind, Western Australia, Australia
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trailing negative sign (2000 SR1)

    Hi d_rasley

    Try this instead:

    =IF(RIGHT(A1)="-",-SUBSTITUTE(A1,"-",""),A1)

Posting Permissions

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