Results 1 to 10 of 10

Changing Position of the Negative Sign

This is a discussion on Changing Position of the Negative Sign within the Excel Questions forums, part of the Question Forums category; I have loaded a report from our system into Excel, and the negative signs are at the end of the ...

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Summit Global Partners
    Posts
    7

    Default

    I have loaded a report from our system into Excel, and the negative signs are at the end of the number (i.e. 35415.01-). How can I remove the negative sign and place it in the front so that Excel knows this is a negative number. The problem is that these numbers are in columns with whole positive numbers in between. I know how to replace/remove the dash, but what I need is some sort of "GoTo" function to find the numbers with the dash, then remove the dash, then put a dash at the beginning of the number. Any suggestions?

  2. #2
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,058

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,894

    Default

    On 2002-04-27 09:51, Brian from Maui wrote:
    Jeff

    Try this thread

    http://www.mrexcel.com/board/viewtop...c=1455&forum=2
    Also you can use ASAP-utilities, free downloadable at:

    http://asap-utilities.com

    which has an option under Numbers for moving - from back to front.

    Aladin

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    On 2002-04-27 09:38, Jeff Steele wrote:
    I have loaded a report from our system into Excel, and the negative signs are at the end of the number (i.e. 35415.01-). How can I remove the negative sign and place it in the front so that Excel knows this is a negative number. The problem is that these numbers are in columns with whole positive numbers in between. I know how to replace/remove the dash, but what I need is some sort of "GoTo" function to find the numbers with the dash, then remove the dash, then put a dash at the beginning of the number. Any suggestions?
    Hi Jeff:
    In addition to the references that Brian and Aladin have given you, you can also use the following formula. Let us say your entry with the negative sign at the end is in cell A2, then in an adjacent cell ...

    =REPLACE("-"&A2,LEN("-"&A2),1,"")+0
    and drag it down to corrspond with all the entries in the adjacent column.

    HTH

    Please post back if it works for you otherwise explain a little further and let us take it from there!

    [ This Message was edited by: Yogi Anand on 2002-04-27 13:57 ]

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,894

    Default



    =REPLACE("-"&A2,LEN("-"&A2),1,"")+0



    Try your formula on the following sample:

    {"12-";
    "13.0-";
    12;
    12.3}

  6. #6
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040

    Default

    Hi,

    Select your range (or modify the routine to find the range) and run this:

    Sub test()
    Dim c As Range
    For Each c In Selection
    If Not c.HasFormula And IsNumeric(c) Then c = CDbl(c)
    Next c
    End Sub

    Bye,
    Jay

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    On 2002-04-27 14:21, Aladin Akyurek wrote:


    =REPLACE("-"&A2,LEN("-"&A2),1,"")+0



    Try your formula on the following sample:

    {"12-";
    "13.0-";
    12;
    12.3}
    Hi Aladin:
    My formula will work only with entries with the - (negative sign) at the end. It will not work with other entries.

    =REPLACE("-"&A2,LEN("-"&A2),1,"")+0 will give -12 for entry 12-

    =REPLACE("-"&A2,LEN("-"&A2),1,"")+0 will give -13 for entry 13.0-

    Regards!

    Yogi Anand


    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,894

    Default

    On 2002-04-27 14:43, Yogi Anand wrote:
    On 2002-04-27 14:21, Aladin Akyurek wrote:


    =REPLACE("-"&A2,LEN("-"&A2),1,"")+0



    Try your formula on the following sample:

    {"12-";
    "13.0-";
    12;
    12.3}
    Hi Aladin:
    My formula will work only with entries with the - (negative sign) at the end. It will not work with other entries.

    =REPLACE("-"&A2,LEN("-"&A2),1,"")+0 will give -12 for entry 12-

    =REPLACE("-"&A2,LEN("-"&A2),1,"")+0 will give -13 for entry 13.0-

    Regards!

    Yogi Anand
    Yogi,

    But the OP said:

    The problem is that these numbers are in columns with whole positive numbers in between.

    So, what about:

    =IF(ISNUMBER(A1),A1,-LEFT(A1,LEN(A1)-1))

    Aladin







  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    Hi Aladin:
    I should have specifically stated that the formula I gave will work only for text entries with the negative sign at the end.

    Regards!
    Yogi Anand

  10. #10
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    I have modified my formula to correctly handle positive numbers, conventional negative numbers, and the negative numbers entered as text with negative sign at the right end. I appreciate Aladin's emphasizing that my formula posted earlier only handled negative numbers entered as text with negative sign at the end.

    =IF(RIGHT(A1,1)="-",REPLACE("-"&A1,LEN("-"&A1),1,""),A1)+0

    so the formula resultw will be as follows ...

    with 12.3 in cell A1 ... result 12.3
    with -12.3 in cell A1 ... result -12.3
    with 12.3- in cell a1 ... result -12.3

    _________________
    Yogi Anand

    Edit: Deleted inactive website from hardcoded signature

    [ This Message was edited by: Yogi Anand on 2003-01-19 13:32 ]

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com