+ Reply to Thread
Results 1 to 6 of 6

Changing the Negative Sign

  1. #1
    tedd13
    Guest

    Changing the Negative Sign

    I have a file that I import into Excel. The negative sign on some of the
    amounts is at the end of the number. Example: 2,778.69-.
    Excel dosn't know that this is a negative amount. Is there an easy way to
    move the sign to the font of the number or to reformat the cell to show that
    it is a negative amount?
    Thanks



  2. #2
    Tom Ogilvy
    Guest

    RE: Changing the Negative Sign

    Dim s as String
    Dim cell as Range
    for each cell in selection.specialCells(xlConstants,xlTextValues)
    s = trim(cell)
    if isnumeric(s)
    cell.value = cdbl(s)
    end if
    Next

    --
    Regards,
    Tom Ogilvy


    "tedd13" wrote:

    > I have a file that I import into Excel. The negative sign on some of the
    > amounts is at the end of the number. Example: 2,778.69-.
    > Excel dosn't know that this is a negative amount. Is there an easy way to
    > move the sign to the font of the number or to reformat the cell to show that
    > it is a negative amount?
    > Thanks
    >
    >


  3. #3
    Bob Phillips
    Guest

    Re: Changing the Negative Sign

    You could transform it into an adjacent column

    =IF(RIGHT(A1)="-",-1*LEFT(A1,LEN(A1)-1),A1)

    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "tedd13" <tedd13@discussions.microsoft.com> wrote in message
    news:C8850EE7-E050-4E96-8563-BACAA9F8308E@microsoft.com...
    > I have a file that I import into Excel. The negative sign on some of the
    > amounts is at the end of the number. Example: 2,778.69-.
    > Excel dosn't know that this is a negative amount. Is there an easy way to
    > move the sign to the font of the number or to reformat the cell to show

    that
    > it is a negative amount?
    > Thanks
    >
    >




  4. #4
    tedd13
    Guest

    RE: Changing the Negative Sign

    Tom,
    That code worked. However, it errors out if it there are no negative signs
    in the column I am looking at. I have six columns that may or may not have
    negative signs.
    Any ideas about how to code around that?
    Thanks

    "tedd13" wrote:

    > I have a file that I import into Excel. The negative sign on some of the
    > amounts is at the end of the number. Example: 2,778.69-.
    > Excel dosn't know that this is a negative amount. Is there an easy way to
    > move the sign to the font of the number or to reformat the cell to show that
    > it is a negative amount?
    > Thanks
    >
    >


  5. #5
    Tom Ogilvy
    Guest

    RE: Changing the Negative Sign

    Dim s as String
    Dim cell as Range, rng as Range
    On Error Resume Next
    set rng = selection.specialCells(xlConstants,xlTextValues)
    On Error goto 0
    if not rng is nothing then
    for each cell in
    s = trim(cell)
    if isnumeric(s)
    cell.value = cdbl(s)
    end if
    Next
    end If

    --
    Regards,
    Tom Ogilvy

    "tedd13" wrote:

    > Tom,
    > That code worked. However, it errors out if it there are no negative signs
    > in the column I am looking at. I have six columns that may or may not have
    > negative signs.
    > Any ideas about how to code around that?
    > Thanks
    >
    > "tedd13" wrote:
    >
    > > I have a file that I import into Excel. The negative sign on some of the
    > > amounts is at the end of the number. Example: 2,778.69-.
    > > Excel dosn't know that this is a negative amount. Is there an easy way to
    > > move the sign to the font of the number or to reformat the cell to show that
    > > it is a negative amount?
    > > Thanks
    > >
    > >


  6. #6
    AA2e72E
    Guest

    RE: Changing the Negative Sign



    format("98.34-","0.00")

    returns -98.34

    works as long as the first argument is numeric to humans

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1