Selected Answer
The most likely source of your problem is the use of the non-breaking space character CHAR(160) , which is what databases like to do. If you have the time to treat your data it can be removed, but then you also might have thousands separators or currency symbols. Therefore I sugged a radical cure that deals with all of these and guarantees the return of a number, to wit, the following UDF.
Function GetValue(Cell As Range) As Double
Const Permitted As String = "01234567890."
Dim Fun As String
Dim Txt As String
Dim Ch As String
Dim n As Long
Txt = Trim(Cell.Value)
For n = 1 To Len(Txt)
Ch = Mid(Txt, n, 1)
If InStr(Permitted, Ch) Then
Fun = Fun & Ch
End If
Next n
GetValue = Val(Fun)
End Function
It must be installed in a standard module. Click Alt+F11 to open the VB Editor. Click on the workbook in the project explorer on the left of the screen. Select Insert > Module and paste the code in the Code area which comes up. After this the workbook must be saved as macro-enabled (xlsm format).
In the worksheet, treat the function like any other. Enter =GetValue(A1) and the cell will display the value of A1. You can treat the result of GetValue like any other number. Multiply, divide, add or subtract, like =GetValue(A1) * 100.