Excel - VBA实例: 字符串截取有效内容

如下字符串：

[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

选中上面单元格内容，然后执行上面函数，得到结果：

牛津联	绿色森林
维尔港	查尔顿
切尔滕	威科姆
福利特	博尔顿