Extract decimal number from an alphanumeric string in Excel VBA

Question

I am really new to excel VBA and struggling to write a function which can extract a decimal number from an alphanumerical string like 1.25/g or 23.5g etc. The decimal number will always be in front.

I found some solutions to the problem but they don't apply to decimal numbers.

I found a solution which works for some strings but doesn't work for all. I have posted the function below. Its returning 1.2 g as 1, but works for a lot of other numbers.

Thanks in advance.

Public Function first_try(a) As Double

Dim i As Long

For i = 1 To Len(a)
If IsNumeric(Mid(a, i, 1)) Then    
    first_try = Val(Mid(a, i))
        Exit For
    End If
Next i

Debug.Print firt_try

End Function

JvdV · Accepted Answer · 2020-03-30 10:24:28Z

1

Well, a shortcut could be Val function.

Public Function first_try(a) As Double
    first_try = Val(a)
End Function

As per documentation:

"The Val function stops reading the string at the first character that it can't recognize as part of a number."

answered Mar 30, 2020 at 10:24

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extract decimal number from an alphanumeric string in Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related