Excel VBA中从字母数字字符串提取十进制数的函数求助

Hey there! Let's sort out that issue with your first_try function. I see the problem—when you have a string like 1.2 g, it's only returning 1 instead of 1.2, right? Let's break down what's going wrong and fix it with a couple of solid solutions.

What's Wrong With the Original Function? #

Your current code finds the first numeric character, then uses Val(Mid(a, i)) to pull the number. The catch is that Val() stops parsing as soon as it hits a non-numeric character (like a space in 1.2 g). Wait, actually Val("1.2 g") should normally return 1.2—so maybe your scenario has a quirk like a space between the number and decimal, or regional settings using commas instead of dots? Either way, we can build a more robust solution.

Solution 1: Use Regular Expressions (Most Reliable) #

Regex is perfect for pattern matching like this—it can easily grab the leading decimal number, even if there's extra characters or spaces after it. Here's a function that works for all your examples (1.25/g, 23.5g, 1.2 g, even negative numbers like -4.6kg):

Public Function ExtractDecimal(inputStr As String) As Double
    Dim regex As Object
    ' Late binding so you don't need to add a reference
    Set regex = CreateObject("VBScript.RegExp")
    
    ' Pattern matches: optional negative sign, digits, optional decimal + more digits
    regex.Pattern = "^-?\d+(\.\d+)?"
    regex.Global = False
    
    If regex.Test(inputStr) Then
        ExtractDecimal = CDbl(regex.Execute(inputStr)(0).Value)
    Else
        ' Return 0 if no number found, or replace with CVErr(xlErrValue) for an error
        ExtractDecimal = 0
    End If
End Function

Solution 2: Improved Loop-Based Method #

If you prefer sticking with a loop (no regex required), we can tweak your original code to collect valid characters (digits and one decimal point) until we hit something that doesn't belong:

Public Function first_try_fixed(inputStr As String) As Double
    Dim i As Long
    Dim numStr As String
    Dim hasDecimal As Boolean
    
    For i = 1 To Len(inputStr)
        Dim currentChar As String
        currentChar = Mid(inputStr, i, 1)
        
        ' Add digits to our number string
        If IsNumeric(currentChar) Then
            numStr = numStr & currentChar
        ' Allow one decimal point (only if we haven't added one yet)
        ElseIf currentChar = "." And Not hasDecimal Then
            numStr = numStr & currentChar
            hasDecimal = True
        ' Stop at any other character
        Else
            Exit For
        End If
    Next i
    
    ' Convert our collected string to a Double, or return 0 if empty
    If numStr <> "" Then
        first_try_fixed = CDbl(numStr)
    Else
        first_try_fixed = 0
    End If
    
    Debug.Print first_try_fixed
End Function

How to Test These #

Just use them like any other Excel function: type =ExtractDecimal(A1) or =first_try_fixed(A1) in a cell, where A1 contains your mixed string. Both will correctly pull the leading decimal number from all your test cases.

内容的提问来源于stack exchange，提问作者Raghav Chamadiya