Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Format cell 0.00 - 0.00% to show a 0 number result (excel xp)

    I have a downloaded csv table from yahoo that shows a stock change cell as 0.00 - 0.00% for a few cells. Excel shows this as an accounting number. How do I format it to show the number 0 and then be able to further have it behave as a normal cell that i can format with a color etc. If I use the formula Left say 4 it will show 0.00 but I can't apply formatting to it as a number ie.-0.97 won't change to (0,14).which becomes irksome esp since i have to apply this formula to all downloaded stock change cells as never know which cell will be 0.00 etc.
    Any better formulas/?
    jerome

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Format cell 0.00 - 0.00% to show a 0 number result (excel xp)

    Hi Jerome,

    If the problem is that some of the data shows 0.00 - 0.00% when it should show 0 (I'm taking your description of the problem literally), why not do a simple search/replace. If the problem is that the 0.00 - 0.00% isn't literal (ie the values vary), you could use a formula to extract the number to the left of the '-' sign, like:
    =IF(ISERROR(SEARCH("-",A1)),A1,LEFT(A1,SEARCH("-",A1)-2)*1)
    where the problem data is in A1.

    Does this achive what you want?

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Format cell 0.00 - 0.00% to show a 0 number result (excel xp)

    Perfect
    Many thanks
    Jerome

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Format cell 0.00 - 0.00% to show a 0 number result (excel xp)

    Actually only works for 0,n/a,and positve number CELLS. GET ERROR VALUE WITH NEGATIVE NUMBERS IE. -0.04 IN A CELL.
    Is there a way of modifying to allow for negative numbers to work
    Jerome

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Format cell 0.00 - 0.00% to show a 0 number result (excel xp)

    OK,
    Slight modification:
    =IF(ISERROR(SEARCH("- ",A1)),A1,LEFT(A1,SEARCH("- ",A1)-2)*1)
    Note spaces after minus signs.
    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Format cell 0.00 - 0.00% to show a 0 number result (excel xp)

    Thanks, but now it does not work for N/A - N/A cells gives value error.
    Sorry to be a pain
    Jerome

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Format cell 0.00 - 0.00% to show a 0 number result (excel xp)

    And now for N/A - N/A:
    =IF(LEFT(A1,3)="N/A","N/A",IF(ISERROR(SEARCH("- ",A1)),A1,LEFT(A1,SEARCH("- ",A1)-2)*1))
    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Format cell 0.00 - 0.00% to show a 0 number result (excel xp)

    Perfect, I think all the bases are covered.
    Many thanks, you are a genius
    Jerome

Posting Permissions

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