Is there a VBA command that will turn a string of letters and numbers into just a number?

[dan7055]

For example, is there a command I can use that will turn '62W' into the integer 62? It would also turn the integer 101 into 101. Basically it just drops any letters out of the string.

 

[chicagocomputerclasses]

You could do this. Keep in mind that all the other characters will be removed as well, so if your text is "A4556.56BH", this will result in 455656

Function StripChar(aText As String)
    Dim I As Integer


    StripChar = ""
    For I = 1 To Len(aText)
        aChar = Mid(aText, I, 1)
        Select Case aChar
            Case "0" To "9"
                StripChar = StripChar & aChar
        End Select
    Next
End Function






Sub macroName()


    Dim txt As String
    txt = "How is 123 a number?"
    finalnumber = Int(StripChar(txt))
    
End Sub

 

[mikerickson]

Val("63H") returns 63
Val("xy123") returns 0
Val("12.6AB") returns 12.6

 

[FlameRetired]

Another way:

This is a variation on a function written by Rick Rothstein.

I've always been rather fond of it. It does not require concatenation.

Please note that it will return a string of text "numbers". If you aren't aware of it putting a "--" preceding the function call in the workbook will coerce the text into actual numeric values. The downside is you will lose any leading zeros and of course any decimals.

Function DigitsOnly(ByVal s As Variant) As Variant 'by Rick Rothstein
Dim i As Long

For i = 1 To Len(s)
If Mid(s, i, 1) Like "[!0-9]" Then Mid(s, i, 1) = Chr(1)
Next i

DigitsOnly = Replace(s, Chr(1), "")

End Function

 

[FlameRetired]

Val("63H") returns 63
Val("xy123") returns 0
Val("12.6AB") returns 12.6

Thank you for that one. I was not aware of this feature of Val. I don't do much VBA. (I "retired" after QBasic ... LOL)

 

[chicagocomputerclasses]

Keep in mind that Val will only work if your string always starts with a number and then text follows it.

 

[mikerickson]

Keep in mind that Val will only work if your string always starts with a number and then text follows it.

Actually, VAL will work if the string starts with a non-numeral. It will return 0, but it won't throw an error like CDbl and other VBA conversion functions.

 

[chicagocomputerclasses]

Actually, VAL will work if the string starts with a non-numeral. It will return 0, but it won't throw an error like CDbl and other VBA conversion functions.

What I was trying to say it won't extract the number out of it. So A123 will return 0, not 123.