Changing negative numbers

R

ryan

I am generating a report which contains negative numbers and positive numbers. After sorting I would like to format the cells so that it replaces all the negative numbers with a zero. Is there anyway to do this?
 
K

kkknie

You can add a column (say B), enter =ABS(A1), copy the entire column
choose paste special and select values. This will give you positiv
numbers. Then just copy to your original range.
 
R

Ron de Bruin

Hi Ryan

Try this

Select the cells and use Edit>Replace

Find what -*
Replace with 0

--
Regards Ron de Bruin
http://www.rondebruin.nl


ryan said:
I am generating a report which contains negative numbers and positive numbers. After sorting I would like to format the cells so
Click to expand...
that it replaces all the negative numbers with a zero. Is there anyway to do this?
 
R

ryan

I was trying to figure out what character I could use in the replace function but I never thought to use
Thanks so muc
 
D

David McRitchie

Since you did say format and not change the values in your report
then the answer is no.

You can hide the true value by making the second part of the
number format a zero the same as you might have for the third part.

An example
_(* #,##0.00_);_(* "0.00"_);_(* "0.00"_);_(@_)

Take the format that comes closest to what you want then modify
it under format, cells, custom

Of course you realize that what you see is not what you have
and you will drive people bonkers trying to figure why totals
don't match.

for more information see table and topics after the table in
http://www.mvps.org/dmcritchie/excel/formula.htm#getformat
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

ryan said:
I am generating a report which contains negative numbers and positive numbers. After sorting I would like to format the cells so
Click to expand...
that it replaces all the negative numbers with a zero. Is there anyway to do this?
 
Top