【EXCEL VBA】TEXTSPLIT関数の使い方と自作方法【徹底解説】

ExcelのTEXTSPLIT関数とは？

TEXTSPLIT関数は、2022年8月にMicrosoft 365に追加された新しい関数で、区切り文字を使ってテキスト文字列を分割することができます。従来のLEFT関数、FIND関数、MID関数などを組み合わせるよりも、簡潔にかつ直感的に文字列を分割できるのが特徴です。

基本的な構文は以下の通りです。

= TEXTSPLIT(text, col_delimiter, [row_delimiter])

引数

text: 分割したいテキストを含むセルを指定します。
col_delimiter: 列方向に分割する区切り文字を指定します。省略した場合の既定値はカンマ（,）です。
row_delimiter: 行方向に分割する区切り文字を指定します。省略した場合、col_delimiterと同じ値が使用されます。

使い方

分割したい文字列を含むセルを選択します。
数式バーに= TEXTSPLIT(と入力します。
分割したいテキストを含むセルを引用符で囲んで入力します。
カンマ（,）を入力します。
列方向の区切り文字となる文字または文字列を引用符で囲んで入力します。
必要に応じて、カンマ（,）を入力して、行方向の区切り文字となる文字または文字列を引用符で囲んで入力します。
Enterキーを押します。

例

A1セルに以下の文字列が含まれている場合：

氏名,年齢,性別
佐藤一郎,30,男性
田中次郎,25,女性
鈴木花子,40,女性

A1セルを選択して、以下の数式を入力すると、以下の結果がスピルとして表示されます。

= TEXTSPLIT(A1, ",")

氏名	年齢	性別
佐藤一郎	30	男性
田中次郎	25	女性
鈴木花子	40	女性

補足

TEXTSPLIT関数は、スピルを返す関数です。スピルとは、複数のセルにまたがる連続した値の範囲を指します。
セル範囲を選択してTEXTSPLIT関数を使用すると、選択範囲全体に結果が出力されます。
行方向の区切り文字を指定する場合、col_delimiterよりもrow_delimiterの方が優先されます。
複数の区切り文字を含む文字列を分割する場合、col_delimiterとrow_delimiterにそれぞれ区切り文字を指定する必要があります。
空白文字も区切り文字として認識されます。
参照されるセル範囲内に空白セルが含まれている場合、その部分のスピル結果は空文字列となります。

TEXTSPLIT関数の代替え手段

2022年8月以降のバージョンのExcelであれば、TEXTSPLIT関数を使用するのが最も簡単で効率的な方法です。しかし、2022年8月以前のバージョンのExcelや、マクロを使用したい場合など、TEXTSPLIT関数を使用できない場合があります。

そのような場合、以下の代替手段を利用することができます。

1. LEFT関数、MID関数、RIGHT関数、FIND関数、SEARCH関数などを組み合わせる

TEXTSPLIT関数と同等の機能を実現するには、複数の関数を組み合わせて使用する必要があります。具体的には、以下の手順で処理を行います。

区切り文字の位置を検索する
- FIND関数またはSEARCH関数を使用して、区切り文字の位置を検索します。
区切り文字ごとに文字列を分割する
- LEFT関数、MID関数、RIGHT関数を使用して、区切り文字ごとに文字列を分割します。
分割結果を配列に格納する
- 分割結果を、配列などのデータ構造に格納します。

この方法は、比較的単純な文字列分割であれば問題ありませんが、複雑な文字列分割や、スピル機能が必要な場合は、煩雑になりがちです。

例：住所を都道府県、市区町村、番地に分ける

以下の表のように、住所を含むセル範囲を選択し、スピル機能を使用して、都道府県、市区町村、番地をそれぞれ別の列に抽出する例を説明します。

| 住所 | |—|—| | 〒100-0001 東京都千代田区千代田1-1-1 | | 〒230-0001 神奈川県横浜市中区本牧1-1 | | 〒540-0001 大阪府大阪市中央区北浜1-1-1 |

数式

以下の数式を、住所を含むセル範囲にスピルで入力します。

都道府県

=LEFT(A1,FIND("都道府県",A1)-1)

市区町村

=MID(A1,FIND("都道府県",A1)+LEN("都道府県")+1,FIND("番地",A1)-FIND("都道府県",A1)-LEN("都道府県")-1)

番地

=RIGHT(A1,LEN(A1)-FIND("番地",A1)+1)

解説

LEFT関数を使用して、最初の**「都道府県」**までの文字列を抽出します。
FIND関数を使用して、「都道府県」と「番地」の位置を検索します。
MID関数を使用して、「都道府県」と「番地」の間の文字列を抽出します。
RIGHT関数を使用して、最後の**「番地」**以降の文字列を抽出します。

補足

この例では、住所の形式が一定であることを前提としています。住所の形式が異なる場合は、数式を調整する必要があります。
複数の区切り文字を指定する場合は、FIND関数とMID関数を組み合わせて使用することができます。
より複雑な文字列分割を行う場合は、正規表現を使用する方が効率的な場合があります。

その他

上記の例は、あくまでも一例です。具体的な状況に合わせて、数式を調整する必要があります。
文字列操作に関する詳細は、Excelのヘルプを参照してください。

LEFT関数、MID関数、RIGHT関数、FIND関数、SEARCH関数などを組み合わせることで、スピル機能を利用した複雑な文字列分割を実現することができます。上記の例を参考に、具体的な状況に合わせて数式を調整し、効率的に文字列を分割してください。

2. VBAマクロを使用する

VBAマクロを使用すれば、TEXTSPLIT関数と同等の機能を柔軟に実現することができます。具体的な方法は、次節で解説します。

TEXTSPLIT関数の自作【VBA】

前述の通り、TEXTSPLIT関数は非常に便利な関数ですが、最新のEXCELを利用していない場合、利用することができません。
具体的には、2022年8月以前のバージョンのEXCELでは利用できません。

そこで、VBAを利用してTEXTSPLIT関数を自作し、EXCELのバージョンに依存せずに使用できる様にする方法を解説したいと思います。

実装

Function MyTextSplit_Spilled(strText As String, strDelimiter1 As String, Optional strDelimiter2 As String) As Variant
    ' 区切り文字の配列
    Dim arrDelimiters() As String
    ' 分割結果の2次元配列
    Dim arr2DResults() As Variant
    ' 区切り文字の数
    Dim intDelimiterCount As Integer
    ' ループカウンタ
    Dim intLoop As Integer
    ' 行カウンタ
    Dim intRow As Integer
    ' 開始位置
    Dim intStartPos As Integer
    ' 区切り文字の位置
    Dim intDelimiterPos As Integer
    ' 結果文字列
    Dim strResult As String

    ' 区切り文字を配列に格納
    ReDim arrDelimiters(1 To 2)
    arrDelimiters(1) = strDelimiter1
    If Not IsMissing(strDelimiter2) Then
        arrDelimiters(2) = strDelimiter2
    End If

    ' 区切り文字の数を取得
    intDelimiterCount = UBound(arrDelimiters)

    ' 分割処理
    intStartPos = 0
    intRow = 0
    intLoop = 0
    Do While intStartPos <= Len(strText)
        ' 次の区切り文字の位置を検索
        intDelimiterPos = -1
        For intIndex = 1 To intDelimiterCount
            intDelimiterPos = InStr(intStartPos + 1, strText, arrDelimiters(intIndex))
            If intDelimiterPos > 0 Then Exit For
        Next intIndex

        ' 区切り文字が見つかった場合
        If intDelimiterPos > 0 Then
            ' 結果文字列に追加
            strResult = Mid(strText, intStartPos, intDelimiterPos - intStartPos)
            ReDim Preserve arr2DResults(intRow, intLoop + 1)
            arr2DResults(intRow, intLoop) = strResult
            intLoop = intLoop + 1

            ' 次の開始位置を更新
            intStartPos = intDelimiterPos + Len(arrDelimiters(intIndex))
        Else
            ' 区切り文字が見つからない場合
            ' 残りの文字列を結果文字列に追加
            strResult = Mid(strText, intStartPos)
            ReDim Preserve arr2DResults(intRow, intLoop + 1)
            arr2DResults(intRow, intLoop) = strResult
            intLoop = intLoop + 1
            intRow = intRow + 1
            Exit Do
        End If
    Loop

    ' 結果を返す
    MyTextSplit_Spilled = arr2DResults
End Function

使い方

このマクロを使用するには、以下の手順に従ってください。

上記のコードをVBAエディタに貼り付けて保存します。
分割したい文字列を含むセルを選択します。
数式バーに以下の数式を入力します。

=MyTextSplit_Spilled(セル参照, 区切り文字1, [区切り文字2])

セル参照は、分割したい文字列を含むセルの参照に置き換えます。
区切り文字1は、列方向の区切り文字に置き換えます。
[区切り文字2]は、オプションで指定する行方向の区切り文字に置き換えます。省略した場合、区切り文字1と同じ値が使用されます。

Enterキーを押します。

例

A1セルに以下の文字列が含まれている場合：

氏名,年齢,性別
佐藤一郎,30,男性
田中次郎,25,女性
鈴木花子,40,女性

A1セルを選択して、以下の数式を入力すると、以下の結果がスピルとして表示されます。

=MyTextSplit_Spilled(A1, ",")

氏名	年齢	性別
佐藤一郎	30	男性
田中次郎	25	女性
鈴木花子	40	女性

解説

前述のコードは、以下の3つの主要な部分から構成されています。

区切り文字と結果の配列を宣言する部分
- arrDelimiters配列には、列方向と行方向の区切り文字を格納します。
- arr2DResults配列には、分割結果の2次元配列を格納します。
分割処理を行う部分
- Do Whileループを使用して、文字列を最後まで処理します。
- ループ内で、以下の処理を行います。
  - 次の区切り文字の位置を検索します。
  - 区切り文字が見つかった場合、結果文字列をarr2DResults配列に追加し、次の開始位置を更新します。
  - 区切り文字が見つからない場合、残りの文字列を結果文字列に追加し、ループを終了します。
結果を返す部分
- MyTextSplit_Spilled関数の戻り値として、arr2DResults配列を返します。

スピル機能の実現

このマクロでは、スピル機能を実現するために、以下の工夫がされています。

arr2DResults配列を2次元配列として宣言し、分割結果を行と列の両方の方向に格納できるようにしています。
Do Whileループ内で、以下の処理を追加しています。
- 区切り文字が見つかった場合、ReDim Preserveステートメントを使用してarr2DResults配列を行方向に拡張し、結果文字列を格納しています。
- 区切り文字が見つからない場合、intRow変数をインクリメントして、次の行に移動しています。