ExcelワークシートでSPLIT関数を使う #Excel

やりたいこと

「,」などの区切り文字を指定して、文字列を簡単に分割するユーザー定義関数を作ります。
Excel VBAではSplit関数を使えますが、Excelシートでは使えません。
具体的には、Googleスプレッドシートのように使えると便利そうです。
https://dekiru.net/article/18395/

縦方向と横方向の分割

さらに、横方向にも縦方向にも分割できるように2つの関数を作ります。
横方向に分割するhSeparate関数と、縦方向に分割するvSeparate関数として、
以下のように作成します。

module1.bas

Public Function hSeparate(ByRef str As String, ByRef sep As String) As Variant
    Dim v As Variant
    v = Split(str, sep)
    hSeparate = v
End Function

Public Function vSeparate(ByRef str As String, ByRef sep As String) As Variant
    Dim v As Variant
    v = Split(str, sep)
    vSeparate = WorksheetFunction.Transpose(v)
End Function

横方向分割の使い方

まずは、横方向に分割するhSeparate関数から説明します。
以下のような分割前の文字列に対して使用します。

戻り値は配列関数になります。
このため、まずは、戻り値の配列を想定した範囲を、選択してから、数式を入力します。

hSeparate("テキスト", "区切り文字")

そして、確定するときにShift+Control+Enterで確定させます。

","で分割できました。

縦方向に分割の使い方

戻り値の配列を想定した範囲を、選択してから、数式を入力します。

vSeparate("テキスト", "区切り文字")

縦方向に、範囲を選択して、Shift+Control+Enterで確定させます。

", "で分割できました。
分割文字を","と" "を組み合わせた2文字にしているので、価格は分割されていません。

GASと同じオプションを付ける場合

GAS(Google Apps Script)のように、以下のようなオプションをつけても使い勝手が良さそうです。
SPLIT（テキスト, 区切り文字, 各文字での分割, 空のテキストを削除）

具体的には、以下のような関数にします。

module2.bas

Public Function gSeparate(str As String, sep As String, bEachSep As Boolean, bEraseBrank As Boolean) As Variant
    Dim v As Variant
    Dim eSep As String, bSep As String
    Dim i As Integer

    '指定された複数の区切り文字を1種類に統一
    bSep = Left(sep, 1)
    If bEachSep = True Then
        For i = 1 To Len(sep)
            eSep = Mid(sep, i, 1)
            str = Replace(str, eSep, bSep)
        Next
    End If

    '文字列を分割
    v = Split(str, bSep)


    '空白を削除
    Dim v_ans As Variant
    ReDim v_ans(LBound(v) To UBound(v))
    Dim cnt As Integer
    cnt = LBound(v)
    If bEraseBrank = True Then
        For i = LBound(v) To UBound(v)
            v(i) = Replace(v(i), " ", "")
            v(i) = Replace(v(i), "　", "")
            If v(i) <> "" Then
                v_ans(cnt) = v(i)
                cnt = cnt + 1
            End If
        Next
        ReDim Preserve v_ans(LBound(v) To cnt - 1)
    End If


    If bEraseBrank = True Then
        gSeparate = v_ans
    Else
        gSeparate = v
    End If

End Function

GASオプション付きの使い方

例1:

戻り値の配列を想定した範囲を、選択してから、以下の数式を入力します。

gSeparate("テキスト", "区切り文字", 各文字での分割, 空のテキストを削除)

Shift+Control+Enterで確定させます。

例2:

以下のような、異なる種類の区切り文字で分割する場合もきれいに分割できます

区切り文字 "/" と"(" と")"のところで分割され、空のセルも自動で削除されています。

例3:

空白を無視したくないときは、空のテキストを削除 = falseと設定します。

この例だと、麻布と大阪の間に、")"と"/"で区切られている部分が空のテキストとして表示されました。