Negative value which isn't a negative value? And how to make it numeric.

[most]

Hi,
Can anyone please explain for me how this is possible and how to make it a numeric.

And no, change it to Number doesn't help, doesn't change it.
As you can see in the formula bar it doesn't contain any minus sign. But it should, because it is a negative value.

regards Marcus

 

[most]

Solved my problem with VBA script, but I still would like to know how it can be minus character there which I can't remove.

Found a script online and modified it for my needs.

Sub ConvertIT()
'Convert text to numeric by copy the value from range to offset 2 columns
  Dim c As Range
  Dim i As Integer
  Dim MyNums As String
For Each c In Range("E2:E1488")
   If Application.WorksheetFunction.IsText(c.Value) Then 'check if cell is text and do some magic
     MyNums = ""
      For i = 1 To Len(c)
       If InStr(1, "0123456789", Mid(c, i, 1), vbTextCompare) > 0 Then
        MyNums = MyNums + Mid(c, i, 1)
       End If
      Next i
    If IsNumeric(MyNums) Then MyNums = -Abs(MyNums) 'make the number negative
    MyNums = MyNums / 100 'fix decimal issue
    c.Offset(0, 2).Value = MyNums 'copy the value to a cell two columns to the right
  Else
    c.Offset(0, 2).Value = c.Value 'if not text just copy
  End If
 Next
End Sub

 

[Rick Rothstein]

Hi,
Can anyone please explain for me how this is possible...

Do you have any Conditional Formatting for that cell (one which adds the negative sign)?

 

[most]

Do you have any Conditional Formatting for that cell (one which adds the negative sign)?

Nope, no Conditional Formatting.

But I just noticed that my other computer shows it correctly.
One is Office 365 Plus and the other one is Office Professional Plus 2016, BUT both are version 16
and English, but they have different regional settings. American 1,234.00 vs Swedish 1 234,00.

On the installation with American settings I can see the minus in the formula bar.
I'm not sure, but I would call this a bug.

Edit: Changed it to Swedish localization it still works fine, O365 issue?!