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.
How to Convert Text Number to Values in Excel