Excel - VBA实例：字符串解析

现有如下数据：

需要将开头和结尾的数字提取出来，作为足球比赛的比分；中间的信息作为盘口信息。

将上面数据copy到Excel中，使用VBA进行处理，代码如下：

 
 
Sub FormatScoreAndPankou()
 
 
 
Dim txtInput As String
 
Dim charSingle
 
Dim char_count
 
Dim result1 As String
 
Dim result2 As String
 
Dim result3 As String
 
Dim step1 As Boolean
 
Dim step2 As Boolean
 
Dim step3 As Boolean
 
 
 
Dim leftNum As Integer
 
Dim rightNum As Integer
 
Dim adjNum As Integer
 
Dim gapNum As Integer
 
 
 
Dim realScore As String
 
Dim realFinal As String
 
Dim pankou As String
 
Dim adjScore As String
 
Dim adjFinal As String
 
 
 
For Each cell In Selection
 
    txtInput = cell.value
 
    
 
    step1 = True
 
    step2 = False
 
    step3 = False
 
   
 
    result1 = ""
 
    result2 = ""
 
    result3 = ""
 
   
 
    For char_count = 1 To Len(txtInput)
 
        charSingle = Mid(txtInput, char_count, 1)
 
        
 
        If step1 Then
 
            If (IsNumeric(charSingle)) Then
 
                result1 = result1 & charSingle
 
            Else
 
                step1 = False
 
                step2 = True
 
            End If
 
        End If
 
        
 
        If step2 Then
 
            If (Not IsNumeric(charSingle)) Then
 
                result2 = result2 & charSingle
 
            Else
 
                step2 = False
 
                step3 = True
 
            End If
 
        End If
 
        
 
        If step3 Then
 
            If (IsNumeric(charSingle)) Then
 
                result3 = result3 & charSingle
 
            Else
 
                step1 = False
 
                step2 = False
 
                step3 = False
 
            End If
 
        End If
 
    Next char_count
 
 
 
    realScore = result1 & "-" & result3
 
    
 
    Select Case result2
 
    
 
    Case "-", "平手", "半球", "平手/半球", "一球", "半球/一球", "一球/球半"
 
    pankou = "1"
 
    
 
    Case "受平手/半球", "受一球"
 
    pankou = "-1"
 
    
 
    Case "球半", "球半/两球"
 
    pankou = "2"
 
    
 
    Case "两球半/三球"
 
    pankou = "3"
 
    
 
    Case Else
 
    pankou = "-100"
 
    
 
    End Select
 
 
 
    leftNum = result1
 
    rightNum = result3
 
    If leftNum > rightNum Then
 
        realFinal = "胜"
 
    ElseIf leftNum = rightNum Then
 
        realFinal = "平"
 
    Else
 
        realFinal = "负"
 
    End If
 
 
 
 
 
    adjNum = pankou
 
    leftNum = leftNum + adjNum
 
    
 
    If leftNum < 0 Then
 
        gap = -leftNum
 
        leftNum = 0
 
        rightNum = rightNum + gap
 
    End If
 
 
 
    adjScore = leftNum & "-" & rightNum
 
    
 
    If leftNum > rightNum Then
 
        adjFinal = "胜"
 
    ElseIf lefNum = rightNum Then
 
        adjFinal = "平"
 
    Else
 
        adjFinal = "负"
 
    End If
 
 
 
    cell.Offset(0, 1) = realScore
 
    cell.Offset(0, 2) = realFinal
 
    cell.Offset(0, 3) = pankou
 
    cell.Offset(0, 4) = adjScore
 
    cell.Offset(0, 5) = adjFinal
 
    
 
Next cell
 
 
 
End Sub

操作前，需要选中需要处理的数据区域，然后运行函数，得到结果如下：