区切り文字を使って文字列を複数のセルへ分割するユーザー定義関数の導入

Excelには多くの便利な関数が備わっていますが、データを特定の区切り文字で分割する標準機能は限られています。特に古いバージョンのExcelでは、新しいTEXTSPLIT関数のような便利なツールは使えません。そんな時、ユーザー定義関数を自作することで、データの分割が必要な状況に柔軟に対応できます。

この関数の実現目的

今回紹介するユーザー定義関数は、任意の区切り文字を用いて文字列データを分割し、指定されたブロックの文字列を取り出すことができます。例えば、住所データがハイフンで区切られている場合に、市区町村部分だけを抽出したいときなどに役立ちます。

この関数は非常に柔軟で、任意の文字列と区切り文字を指定するだけで、簡単にデータを分割できます。また、存在しないブロック番号を指定した場合には、自動的に空白を返すため、エラーが発生することなく安全に使用することが可能です。

関数コードの登録方法

ExcelのVBAエディタを使用して、ユーザー定義関数を簡単に登録することができます。まずはAlt + F11キーでVBAエディタを開き、新しい「標準モジュール」を挿入して、提供された関数コードを貼り付けます。

以下のコードを「標準モジュール」へ貼り付けてください。貼り付け後はVBAエディタは閉じます。

Function SplitTextByDelimiter(rng As Range, delimiter As String, blockNumber As Integer) As String
    Dim textParts() As String
    textParts = Split(rng.Value, delimiter)
    
    If blockNumber > 0 And blockNumber <= UBound(textParts) + 1 Then
        SplitTextByDelimiter = textParts(blockNumber - 1)
    Else
        SplitTextByDelimiter = "" ' 指定されたブロックが存在しない場合は空白を返す
    End If
End Function

関数の使用方法・基本

上記のセルB2へ、2行目の1ブロック目を表示させる計算式をセットする手順を検討してみましょう。

関数を登録した後、Excelのセルに直接関数を入力するか、「挿入」タブから「関数の挿入」ボタンをクリックし、「ユーザー定義関数」グループから選択することができます。

上記のサンプルコードでは「SplitTextByDelimiter」という関数名になっています。

関数の形式は以下の通りです：

=関数名(元データ, "区切り文字", ブロック番号)

=SplitTextByDelimiter(Rng,Delimiter,BlockNumber)

実際に使用する際は、元データには分割したい文字列が含まれたセルを、区切り文字には使用する区切り文字を、ブロック番号には取り出したいブロックの番号を指定します。具体的には以下のような設定になります。

=SplitTextByDelimiter(A2,"-",1)

あとは引数「Rng」や「BlockNumber」を適切に変えればよいでしょう。

関数の使用方法・絶対参照の活用

また、下図のように列の見出しにブロック番号の数値を指定すれば式のコピーが楽になります。

この表形状ならばセルのB2へ以下の絶対参照付き計算式を設定することでコピーが楽になります。

=SplitTextByDelimiter($A2,"-",B$1)

この式の $A2 は、列 A が絶対参照されています。これは、この関数が常に列 A の同じ行のデータを参照することを意味しています。したがって、この式を列 B の下にある任意のセルにコピーすると、コピー先のセルは同じ行の列 A のデータを参照し続けます。一方、行の数字（2）は相対参照なので、式を下にコピーすると参照する行が動的に変わります。

次に、B$1 の部分ですが、これは行番号 1 を絶対参照しています。列の参照は相対的です。これは、この関数が常に行 1 の同じ列のデータ（この場合はブロック番号）を参照することを意味しています。よって、この式を横にコピーすると、コピー先のセルは行 1 の次の列のデータを参照し続けます。

この絶対参照と相対参照の組み合わせによって、B2のセルに入れた式を右にも下にもコピーすることで、元データから適切なブロックの値を抽出することができるようになります。各セルが適切な元データとブロック番号を参照し続けるため、関数は正確な情報を提供し続けることができるのです。

実行後の確認事項

関数を実行すると、指定したブロックのデータがセルに表示されます。複数のセルに関数を適用する場合は、セルを選択してドラッグすることで、すべてのセルに同じ関数を適用することができます。

また、この関数はユーザー定義VBA関数であるため、マクロが無効になっていると使用することができません。Excelのセキュリティ設定を確認し、マクロを有効にする必要があります。また、関数を共有する際には、マクロを含むファイルであることを受取人に伝え、信頼できるソースからのファイルであることを確認してもらうことが大切です。