示例詳解VBA的Split函數

excelperfect

標簽：VBA，Split函數

使用VBA時，有可能需要根據分隔符將字符串拆分為不同的部分。此時，就可以使用VBA的Split函數。

Split函數是ExcelVBA中的內置字符串函數，可用于根據分隔符拆分文本字符串。

Split函數語法

Split(Expression,[Delimiter],[Limit],[Compare])

其中，

1.參數Expression，必需，指定要基于分隔符拆分的字符串。如果是一個長度為零的字符串（“”），SPLIT函數將返回一個空數組。

2.參數Deimiter，可選，指定用于拆分“Expression”參數的分隔符。如果不指定此參數，則空格字符被視為默認分隔符。如果給出一個長度為零的字符串（“”），函數將返回整個“Expression”字符串。

3.參數Limit，可選，指定要返回的子字符串的總數。例如，如果只想從“Expression”參數返回前三個子字符串，那么應該是3。如果不指定此參數，默認值為-1，返回所有子字符串。

4.參數Compare，可選，可以指定在評估子字符串時希望SPLIT函數執行的比較類型。以下選項可用：0，執行二進制比較，意味著區分大小寫；1，執行文本比較，意味著不區分大小寫。

示例1：拆分句子中的單詞

假設有一段文本：“This is a goodidea”，可以使用Split函數將這個句子中的每個單詞作為數組中單獨項。

Sub SplitWords()

    Dim strText As String

    Dim strResult() As String

    Dim i As Long

    Dim str As String

    strText = 'This is a good idea'

    strResult() = Split(strText)

    For i = LBound(strResult) ToUBound(strResult)

        str = str & strResult(i) &vbCrLf

    Next i

    MsgBox '拆分的單詞：' &vbCrLf & str

End Sub

運行程序后的結果如下圖1所示。

圖1

在本示例中，只指定了第一個參數，即要拆分的文本。由于未指定分隔符，因此將空格字符作為默認分隔符。

注意：VBA Split函數返回索引基于0開始的數組。此外，將Split函數的結果賦值給數組時，該數組必須聲明為字符串數據類型。如果將其聲明為變量數據類型，則會顯示類型不匹配錯誤）。在上面的示例中，已將strResult()聲明為字符串數據類型。

示例2：統計句子中的單詞數

可以使用Split函數來獲取一個句子中的單詞總數，也就是計算拆分文本得到的數組中的元素數。

Sub CountWords()

    Dim strText As String

    Dim strResult() As String

    Dim lngWordNum As Long

    strText = 'This is a good idea'

    strResult = Split(strText)

    lngWordNum = UBound(strResult) + 1

    MsgBox '句子中的單詞數：' &lngWordNum

End Sub

程序運行后的結果如下圖2所示。

圖2

在這種情況下，UBound函數告訴該數組的上限（即數組的最大元素數）。由于數組的索引基于為0，因此加1以獲得總單詞數。

可以使用類似的代碼在VBA中創建一個自定義函數，該函數將文本作為輸入并返回單詞數。

Function GetWordNum(rngCellRef As Range)

    Dim strText As String

    Dim strResult() As String

    strResult =Split(WorksheetFunction.Trim(rngCellRef.Text), ' ')

    GetWordNum = UBound(strResult) + 1

End Function

這樣，可以在工作表中像使用Excel內置函數一樣使用GetWordNum函數，如下圖3所示。

圖3

示例3：使用空格字符以外的分隔符

在前面的兩個示例中，Split函數只使用了一個參數，其余的都是默認參數。如果要使用其他分隔符，那么需要在Split公式中指定該分隔符。

在下面的代碼中，Split函數基于逗號作為分隔符返回數組。

Sub SplitWithComma()

    Dim strText As String

    Dim strResult() As String

    DimstrDisplay As String

    Dim i As Long

    strText = 'This,is,a,good,idea'

    strResult = Split(strText, ',')

    For i = LBound(strResult) ToUBound(strResult)

        strDisplay = strDisplay &strResult(i) & vbNewLine

    Next i

    MsgBox '拆分的單詞：' &vbNewLine & strDisplay

End Sub

運行后的結果如下圖4所示。

圖4

示例4：拆分句子為指定數量

通過Split函數，可以指定希望獲得的拆分次數。例如，如果沒有指定任何內容，分隔符的每個實例都將用于拆分字符串。

然而，如果指定3作為限制，那么字符串將只分為三部分。

Sub SplitWithCommaSep()

    Dim strText As String

    Dim strResult() As String

    Dim strDisplay As String

    Dim i As Long

    strText = '中國,湖北宜昌,西陵區,443000'

    strResult = Split(strText, ',',3)

    For i = LBound(strResult) ToUBound(strResult)

        strDisplay = strDisplay &strResult(i) & vbNewLine

    Next i

    MsgBox strDisplay

End Sub

運行程序后的結果如下圖5所示。

圖5

如果想要將單行地址拆分為消息框中顯示的格式時，可以使用。然后，可以創建一個自定義函數，該函數將返回分為三部分的地址（每一部分在新行中）。

Function SplitAddress(rngCellRef As Range)

    Dim strText As String

    Dim strResult() As String

    Dim strDisplay As String

    Dim i As Long

    strResult = Split(rngCellRef, ',',3)

    For i = LBound(strResult) ToUBound(strResult)

        strDisplay = strDisplay &Trim(strResult(i)) & vbNewLine

    Next i

    SplitAddress = Mid(strDisplay, 1,Len(strDisplay) - 1)

End Function

這樣，可以在工作表中像使用Excel內置函數一樣使用SplitAddress函數，如下圖6所示。

圖6

示例5：獲取文本中指定的字符串

使用VBA中的Split函數，可以指定要使用結果數組的哪個部分。

下面是一個自定義函數的代碼，可以在其中指定一個數字，它將從數組中返回該元素。例如，如果想要城市名，可以指定2（因為它是數組中的第2個元素）。

Function GetNthElement(rngCellRef As Range, num As Integer)

    Dim strResult() As String

    strResult = Split(rngCellRef,',')

    GetNthElement = strResult(num - 1)

End Function

上面的函數有兩個參數，一個是包含地址的單元格引用，另一個是要返回的元素位置，Split函數拆分并將其賦值給結果變量。

然后返回指定為第二個參數的元素。注意，由于索引基數為0，所以使用num-1正確返回所需元素。

示例結果如下圖7所示。

圖7

注意，當所有地址的格式都一致時，這個自定義公式最合適——即城市總是在第一個逗號后出現。如果數據不一致，就不會得到想要的結果。

其實，上面的代碼可以進一步簡化為：

Function GetNthElementAdd(rngCellRef As Range, num As Integer)

    GetNthElementAdd = Split(rngCellRef,',')(num - 1)

End Function