Change + to -

[Ulf NIlsson]

Hi,

If a result in a cell is for example 10,
I want the result to be -10.
If the result in a cell is -8,
I want the result to be 8.

How is this done?

/ Ulf

 

[Stefan Hägglund [MSFT]]

Hi Ulf!

You can use the following formula (value in cell A1) in an empty cell:
=A1*-1
Then copy this cell and use Paste Special, Values, to remove the formula

Best regards

Stefan Hägglund
Microsoft

 

[Frank Kabel]

Hi
try
=-Your_Formula

 

[Biff]

Hi Ulf!

Here's one way if the value is the result of a formula:

=IF(your formula=0,0,IF(your formula<0,your formula*-
1,your formula*-1))

Or someone might be able to come up with a custom format.

Biff

 

[Ulf Nilsson]

Thanks for your answers.

I agree with Biff:
The best would be if Excel had a custom format to fix
this, since data is often imported from different
databases which are an income for the firm but an expense
for the employees.

/ Ulf

 

[David McRitchie]

The following will work and should be a bit neater,
in the results, and it will retain formulas rather than
converting to values:
Place -1 in an empty cell
Select the area to be changed
Edit, Paste Special, Multiply
Unfortunately it will convert blank cells to zero

To avoid that you can after choosing the selection area
Ctrl+G (edit, go), Formulas
Edit, Paste Special, Multiply
Doesn't matter what you choose for formulas it will
only process cells that look like numbers.

and then do the constants
reselect the original selection area
Ctrl+G (edit, go), Constants
Edit, Paste Special, Multiply

Progression of multiple changes to a formula
=1+1
=(1+1)*-1
=((1+1)*-1)*-1

Progression of multiple changes to a constant
3
-3
3

 

[David McRitchie]

Just seemed that was a lot of bother just to change the sign
of the cells in a range, so wrote a macro. It does not fully check
that parens are matched and in correct order though, so stripping
parens could result in a an error in the formula. Perhaps something
could be done better with Regular Expressions.\

Sub ChangePlusMinus()
'D.McRitchie, 2004-08-11 excel.misc
'-- www.mvps.org/dmcritchie/excel/numconv.htm
'-- Additional code added to check parens to not misprocess
'-- =(A1+1)*(B1+1) * -1 --- but is not foolproof i.e. =((a1+1))*...
'-- Will not treat ="1"&"2" as a number
'-- Best for Constants and avoiding Empty cells
Dim cell As Range, txt As String
On Error Resume Next
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants, 1))
cell.Value = cell.Value * -1
Next cell
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas, 1))
If Right(cell.Formula, 6) = ") * -1" And _
InStr(3, cell.Formula, "(") <= InStr(3, cell.Formula, ")") And _
Left(cell.Formula, 2) = "=(" Then
cell.Formula = "=" & Mid(cell.Formula, 3, Len(cell.Formula) - 8)
Else
cell.Formula = "=(" & Mid(cell.Formula, 2) & ") * -1"
End If
Next cell
End Sub