现有如下数据:
0半球2 |
2-1 |
0-0 |
0受一球1 |
1半球1 |
5球半/两球0 |
4两球半/三球1 |
3平手2 |
2一球0 |
3-2 |
0平手1 |
3半球0 |
0平手1 |
1受平手/半球2 |
1一球2 |
4球半0 |
2半球/一球0 |
2一球1 |
1球半/两球0 |
0一球/球半1 |
3半球/一球1 |
0半球/一球1 |
需要将开头和结尾的数字提取出来,作为足球比赛的比分;中间的信息作为盘口信息。
将上面数据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
操作前,需要选中需要处理的数据区域,然后运行函数,得到结果如下: