Displayed value different to actual value due to custom format

[ExcelMedium]

I have a file which shows monetary values in one column. The majority have a number format (positive values - these are correct). Others have a custom format which shows them as - numbers eg -36.50 (but these numbers are actually positive). How can I easily make the negative ones actually negative. I've tried to look for characters etc using formulae but the - is completed ignored.

 

[Rick Rothstein]

Here is a macro that should work for you... simply select the range containing the numbers (it is okay to include the non-negative values in that selection) and run this macro.

Sub MakeNegativeFormattedNumberReallyNegative()
  Dim Cell As Range
  For Each Cell In Selection
    If Left(Cell.Text, 1) = "-" Then
      Cell.Value = -1 * Cell.Value
      Cell.NumberFormat = "General"
    End If
  Next
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (MakeNegativeFormattedNumberReallyNegative) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[ExcelMedium]

Thank you so much! I had to add a - into the line cell.value=-1*-cell.value for some reason
It now works a treat

 

[ExcelMedium]

Funny but had to remove the - again. Working now - will save hours! Thank you

 

[Rick Rothstein]

Thank you so much! I had to add a - into the line cell.value=-1*-cell.value for some reason
It now works a treat

Funny but had to remove the - again. Working now - will save hours! Thank you

I am not sure why you thought you needed to add the minus sing originally... was sure what I posted should have always worked. Can I assume all is good now?

 

[ExcelMedium]

I am not sure why you thought you needed to add the minus sing originally... was sure what I posted should have always worked. Can I assume all is good now?

Yes - fabulous. Many thanks