Changing negative numbers

[Guest]

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?

 

[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.

 

[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

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?

 

[kkknie]

I feel like a dunderhead for not realizing that would work!

 

[Guest]

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

 

[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

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?