如何在含多个分隔符的字符串中提取指定标识与分隔符间的数值？VBA函数问题排查与解决

The problem with your current function is that Application.WorksheetFunction.Search(endval, infor) always targets the first occurrence of your delimiter (-~~-) in the entire string—no matter where your target findVal is located. When trying to extract SecondValue or ThirdValue, this leads to wrong position calculations (and even errors if the end position ends up before the start position).

Modified Working Function #

Here's an updated version that fixes this by starting the search for the delimiter right after the position of your target label:

Function GetValue(infor As String, findVal As String, endval As String) As String
    Dim startPos As Long
    Dim endPos As Long
    
    ' Locate the position immediately after the target label (e.g., "FirstValue:")
    startPos = Application.WorksheetFunction.Search(findVal, infor) + Len(findVal)
    
    ' Find the first delimiter that comes AFTER the start position
    endPos = Application.WorksheetFunction.Search(endval, infor, startPos)
    
    ' Extract and clean up the substring between the two positions
    GetValue = Trim(Mid(infor, startPos, endPos - startPos))
End Function

Key Fixes & Improvements: #

• Added startPos to mark the exact point right after your target label, so we don't look back at earlier parts of the string
• Used the third argument of Search to anchor the delimiter search to startPos, ensuring we grab the correct separator for each value
• Added Trim() to remove any extra spaces around the extracted value (optional but keeps results clean)

Test the Updated Function #

You can verify all three values work with this test subroutine:

Sub testGetValue()
    Dim inputText As String
    inputText = "FirstValue: 1234 lb -~~- SecondValue: 555 oz -~~- ThirdValue: 123 gal -~~-"
    
    Debug.Print "First Value: " & GetValue(inputText, "FirstValue:", "-~~-")
    Debug.Print "Second Value: " & GetValue(inputText, "SecondValue:", "-~~-")
    Debug.Print "Third Value: " & GetValue(inputText, "ThirdValue:", "-~~-")
End Sub

Expected Output: #

First Value: 1234 lb
Second Value: 555 oz
Third Value: 123 gal

Robust Optional Add-On (Error Handling) #

If you want to handle cases where the target label or delimiter isn't found, add error handling to avoid runtime errors:

Function GetValueSafe(infor As String, findVal As String, endval As String) As String
    On Error Resume Next
    Dim startPos As Long
    Dim endPos As Long
    
    startPos = Application.WorksheetFunction.Search(findVal, infor) + Len(findVal)
    endPos = Application.WorksheetFunction.Search(endval, infor, startPos)
    
    If Err.Number = 0 Then
        GetValueSafe = Trim(Mid(infor, startPos, endPos - startPos))
    Else
        GetValueSafe = "Value Not Found" ' Or return an empty string: ""
    End If
    On Error GoTo 0
End Function

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