Excel 2010 VBA: Cleaning text to convert to a number

Topic

New Reply

Aloha,
I have a VBA problem and I hope there’s an easy solution. I have a macro I use to clean and format raw data input by real estate agents. One particular field is supposed to be a percentage, but there are no input controls so agents can type in any odd text, and they do. My problem comes when I encounter a value like “2.5% SS” or “2.5+GET” or “2.5% & GET”. I haven’t figured how to clean and convert such a value to 0.025 without generating a Type Mismatch error. Val(“2.5 GET”) works but Val(“2.5% & GET”) errors. So far I’ve learned that “%”, “+”, and “&” cause the error, but I have no idea which or how many other characters will cause errors. Seems silly and inelegant to run each value through a series of replaces like Replace(c.Formula, “+”, “”) before using Val(c.Formula).

Is there a more reliable way to convert “2.5% & GET” to 0.025 ?

Mahalo for your feedback,
JohnJ

Reply | Quote

Replies

Can you use something like this function? The Sub is just to demo what it does.

Code:

Sub TEST_StripText()
MsgBox StripText("1,23abc4.56def", True)
MsgBox StripText("1,23abc4.56def", False)
End Sub

Function StripText(strSource As String, Optional blnTruncate As Boolean = True) As String
Dim strNew As String, intCount As Integer
Const strNumeric = "0123456789.,%"
For intCount = 1 To Len(strSource)
    If InStr(1, strNumeric, Mid(strSource, intCount, 1)) > 0 Then
        strNew = strNew & Mid(strSource, intCount, 1)
    Else
        If blnTruncate Then Exit For
    End If
Next
StripText = strNew
End Function

Reply | Quote

Beautiful, thank you.
JohnJ

Reply | Quote