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

Topic

New Reply

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

Reply | Quote

Replies

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
[Fmr MS MVP - Word]

Reply | Quote

Perfect
Many thanks
Jerome

Reply | Quote

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

Reply | Quote

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

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

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

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

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

Reply | Quote