Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Post Excel 2010 VBA: Cleaning text to convert to a number

    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

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    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

  3. The Following User Says Thank You to jscher2000 For This Useful Post:

    JohnJacobson (2011-09-01)

  4. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Beautiful, thank you.
    JohnJ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •