Extract numericalvalue with decimal point from alphanumerical string

[syp1677]

I trying to extract numerical string from alphanumeric string by this:

s = "1.234 gm"
            For k = 1 To Len(s)
                If IsNumeric(Mid(s, k, 1)) Then
                   r = r & Mid(s, k, 1)
                End If
            Next k
MsgBox "Numerical value is" & r

I get output "Numerical value is 1234" instead of 1.234. Any suggestions ..

 

[Marc L]

Such bad logic here as you forgot the decimal separator ‼ ​

​

As explained in VBA help - a must read ! - several ways without an useless loop :​

• if the text always ends with " gm" so just use the VBA text function Replace to replace it with an empty string.
  
  
• If the text always ends with 3 characters so just use the VBA function Len to count the string characters
  then use the VBA text function Left to extract the first count - 3 characters.
  
  
• Use the VBA function InStr to find the space position then use the function Left upon the space position - 1 …
  
  
• Use the first element returned via the VBA text function Split …
  
  
• (Update !) And now the classic VBA function with a dot as the decimal separator, the easy way to go : Val !

 

[syp1677]

Hi Marc,

Thanks again for guidance.

• Text doesn't always end with "gm"
• Text doesn't always ends with 3 characters
• I tried using InStr and left :
  • r = Left(s, (Len(s) - (Len(s) - InStr(s, " "))))
  • It gave value 1 instead of 1.234
• I tried using Split function:
  • r = Split(s, " ")(0)
  • It gave value 1 instead of 1.234

I am learning VBA by example and internet and I know I have try real hard.

 

[Marc L]

For Instr & Left the logic is not respected (why Len ?!) …​

​

Good logic for Split even if the space as delimiter is useless 'cause this is the default delimiter​

so to confirm it well works and you have badly declared the variable r just try this : MsgBox Split(s)(0) …​

​

 

[Marc L]

Post #2 edited 'cause I forgot the classic obvious VBA function …​

​

 

[rlv01]

Another way to do this is by using regular expressions.

Sub CalcTest()
    Dim S As String, R As String, D As Double

    S = "1.234 gm"
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[^.0-9]"
        R = .Replace(S, "")
    End With

    If IsNumeric(R) Then
        D = Val(R)
        MsgBox "Input String: " & S & vbCr & vbCr & "Numerical value is: " & D
    Else
        MsgBox "Input String: '" & S & "' is not a number"
    End If
End Sub

 

[Marc L]

Yes but not at beginner level and as here the way to go is VBA function Val …​

​