如下字符串:
[12]牛津联 | 绿色森林[24] |
[15]维尔港 | 查尔顿[11] |
[19]切尔滕 | 威科姆[10] |
[16]福利特 | 博尔顿[07] |
中括号的内容是这个球队的联赛排名,要保留球队名字,把联赛排名去掉。
VBA代码如下:
Sub FormatTeamName()
Dim strValue As String
Dim pos1 As Integer
Dim pos2 As Integer
Dim length As Integer
Dim charCount As Integer
Dim exception As Boolean
For Each cell In Selection
result = ""
exception = False
strValue = cell.value
strValue = Trim(strValue)
length = Len(strValue)
'Find the [], and delete it
pos1 = InStr(strValue, "[")
pos2 = InStr(strValue, "]")
If pos1 = 0 Or pos2 = 0 Then
GoTo NextIteration
End If
If pos1 = 1 Then
strValue = Mid(strValue, pos2 + 1, length - pos2)
Else
strValue = Mid(strValue, 1, length - (pos2 - pos1 + 1))
End If
'Check if this is special case
Select Case strValue
Case "汉诺威96"
exception = True
End Select
If Not exception Then
length = Len(strValue)
'Delete the digit number in the begin
For char_count = 1 To length
charSingle = Mid(strValue, char_count, 1)
If Not IsNumeric(charSingle) Then
Exit For
End If
Next
strValue = Mid(strValue, char_count, length - char_count + 1)
length = Len(strValue)
'Delete the digit number in the end
For char_count = 1 To length
charSingle = Mid(strValue, length - char_count + 1, 1)
If Not IsNumeric(charSingle) Then
Exit For
End If
Next
strValue = Mid(strValue, 1, length - char_count + 1)
End If
cell.value = strValue
NextIteration:
Next cell
End Sub
选中上面单元格内容,然后执行上面函数,得到结果:
牛津联 | 绿色森林 |
维尔港 | 查尔顿 |
切尔滕 | 威科姆 |
福利特 | 博尔顿 |