How to Convert Text Number to Values in Excel

Today I spent time working out why I could not convert some text values I had pasted from a FR report to Excel  into numbers. It turns out the text values were padded with non breaking spaces which TRIM() did not remove. I wrote the following code to deal with this problem. You can add it to a module in your workbook and call it like this =getValue(B4) in Excel. Hopefully this will save someone some time .

Option Explicit
'***
'Converts a number in a text string into a value
'
Function getValue(Text As String)
    'Replaces non breaking spaces
    Text = Replace(Text, Chr(160), "")
    'Trims leading and trailing spaces
    Text = Trim(Text)
    'Replaces thousands separator assuming this is the same as Excel
    Text = Replace(Text, ThousandsSeparator(), "")
    getValue = Val(Text)
End Function
'***
'Returns the current Excel thousands separator character.
'
Function ThousandsSeparator() As String
    Dim appUse As Boolean

    appUse = Application.UseSystemSeparators
    Application.UseSystemSeparators = True
    ThousandsSeparator = Application.International(xlThousandsSeparator)
    Application.UseSystemSeparators = appUse
End Function

If you want any help with Oracle EPM please contact me.