Excel VBA 使用 Split 函數分割字串、放入陣列教學與範例

在 Excel 中使用 VBA 的 Split 函數，將字串依照特定字元切割後，轉換為字串陣列。

問題

假設我們的原始資料包含了許多的欄位，不同的欄位之間以斜線分隔，該如何將這樣的資料切開，放在個別的儲存格中？

解法

若要依照指定的字元分割字串，有兩種方式可以使用，第一種是結合 SEARCH、LEFT、RIGHT 與 MID 這類的 Excel 函數，取出特定位置的子字串，但是這種方式的限制較多，無法處理太複雜的資料。

另外一種則是使用 VBA 的 Split 函數，將字串直接依照特定字元分割成陣列，這種作法比較可以適用於複雜的資料，在資料長度比較長的時候，處理起來也很簡單。

使用 Excel 函數分割字串

對於比較簡單的資料來說，可以結合各種 Excel 函數來處理，這種方式適合只熟悉 Excel 的公式，不會使用 VBA 的人。

如果要取出原始資料中以斜線分割的第一個欄位，可以先使用 SEARCH 函數找出第一個斜線的位置，接著再用 LEFT 函數擷取從資料開頭到第一個斜線中間的資料：

=LEFT(A2, SEARCH("/",A2,1)-1)

若要取出第二個欄位，也是採用類似的方式，只不過我們這時候必須以 SEARCH 找出第一個斜線與第二個斜線的位置，然後使用 MID 函數將兩個斜線之間的資料取出來：

=MID(A2,SEARCH("/",A2,1)+1,SEARCH("/",A2,SEARCH("/",A2,1)+1)-SEARCH("/",A2,1))

這裡 MID 函數的第二個參數中，我們使用 SEARCH 函數找出第一個斜線的位置，從第一個線之後開啟擷取資料。而 MID 的第三個參數中，我們先使用兩個 SEARCH 找出第二個斜線的位置（先找第一個，再從第一個往後找第二個），接著再減掉第一個斜線的位置，就得到第二個欄位的資料長度。

有了第二個欄位的起始位置與長度之後，就可以用 MID 取出資料了。

要取出第三個欄位的話，可以先用兩個 SEARCH 找出第二個斜線的位置，再用 LEN 計算出整個資料的長度，然後減去第二個斜線的位置，就可以得到第三個欄位的資料長度，最後再使用 RIGHT 函數從右方開始取出第三個欄位的資料（因為第三個欄位在這裡剛好是最後一個欄位，所以可以這樣做）：

=RIGHT(A2,LEN(A2)-SEARCH("/",A2,SEARCH("/",A2,1)+1))

雖然結合各種 Excel 的函數後，可以正確擷取出各個欄位的資料，但是如果欄位的數量很多的時候，這種作法就會變得非常複雜，這時候就建議改用 VBA 的 Split 函數來處理。

使用 VBA 的 Split 函數分割字串

VBA 的 Split 函數是專門用來分割字串用的，使用上雖然需要撰寫一些 VBA 程式碼，但是卻可以非常快速的處理大量欄位的資料，在資料複雜時相當好用。
Step 1
參考 啟用 Excel「開發人員」工具教學，啟用 Excel 內建的開發人員工具列。

Step 2
選擇 Excel 工具列的「開發人員」頁籤，點選「巨集」功能。

Step 3
輸入巨集的名稱（自己取一個名字），然後按下「建立」。

Step 4
在 VBA 程式碼編輯器中，撰寫分割欄位的 VBA 程式碼。

這裡我們使用一個 For 迴圈，逐一將每一列（row）的原始資料取出來，使用 Split 自動將所有的欄位以斜線分割成一串陣列，然後再用第二個迴圈將每個欄位的資料填回 Excel 表格中對應的位置：

Sub MySplit()

For i = 2 To 8
  ' 取得原始資料
  rawData = Cells(i, 1)

  ' 使用 Split 分割欄位
  fieldArray = Split(rawData, "/")

  ' 將各個欄位填入對應的儲存格
  For j = 0 To 2
    Cells(i, j + 2).Value = fieldArray(j)
  Next j
Next i

End Sub

Step 5
執行撰寫好的 VBA 程式之後，就可以得到所有欄位的分割結果了。

使用 VBA 的作法雖然需要撰寫一些程式，但是只要程式寫好之後，不管資料欄位有多少，都可以一次處理掉，實務上來說會比單純使用 Excel 函數的解法更實用。

參考資料：TechOnTheNet、Exceljet、Excel Trick、Microsoft 官方網站、Office 官方網站