【Excel】関数を使って特定の文字で区切られた３つの文字列を分ける方法（VBAパターンも）

とあるシステムから書き出したデータが、よくわからない記号（タブ記号かと思ったらそうでもない謎の記号）で区切られた３つの文字列のデータで、これを分割したいと思ってCopilotに尋ねました。

LEFT関数やらRIGHT関数を使っていろいろするのはだいたい想像ついていたのですが、覚えるのが厄介な上にまた使う機会がありそうだったのでここで書いておきます。また調べたりプロンプト入れるの面倒ですしね。

サンプル

セルA1に「ほげ|ふが|ぴよ」という文字列があります。

これを「|」で分割してB1、C1、D1にそれぞれ表示します。

１つ目

=LEFT(A1, FIND("|", A1) - 1)

LEFT関数は第１引数で対象の文字列、第２引数で何文字目から左を取得するかを指定します。

FIND関数は第１引数で検索する文字列、第２引数で対象の文字列を指定し、検索した文字列が何文字目に出てきたかを数値で返します。

今回のサンプルでは左から３文字目に「|」が出てくるので3が返されます。ただ、このままだと「ほげ|」になってしまうので、-1して２文字目から左を返すという風にします。

２つ目

=MID(A1, FIND("|", A1) + 1, FIND("|", A1, FIND("|", A1) + 1) - FIND("|", A1) - 1)

ややこしいのは２つ目です。

MID関数は第１引数で文字列、第２引数で開始位置、第３引数で文字数を指定します。今回の例では第２引数で4、第３引数で2を指定できればOKです。

第２引数の開始位置は、FIND関数を使って「|」を検索します。これだけだと最初の「|」を含んでしまうので+1して「ふ」から開始するようにします。

第３引数の文字数ですが、FIND関数は第３引数で開始位置を指定することができます。これで「ふ」以降に出てくる「|」を探せるようになり、返ってくる値は「ほげ|ふが|」の６文字目ということで6です。

この内、「ほげ|」の３文字は不要なのでFIND関数を使って3を引き、また「ふが|」の最後の「|」は不要なので-1して2を求めて「ふが」を出力できました。

３つ目

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

RIGHT関数は第１引数で文字列、第２引数で何文字目から右側を取得するかを指定します。

LEN関数は指定した文字列が何文字あるかを返します。サンプルでは8が返ります。この中から２つ目の「|」が何文字目に出てくるのかを調べて、その値を全体の文字数8から引けばOKです。

２つ目の「|」を求める方法は、１つ目の「|」の次の文字以降に出てくる「|」の位置を調べればよくて、この２つ目の「|」の次の文字が何文字目かを出すために+1して返ってくる値は6となります。

これで右から8-6=2文字ということで「ぴよ」が取り出せましたとさ。

VBAでやる場合

区切りがこれ以上増えても繰り返しFIND関数で求めていけば分けることができますが、めちゃくちゃ長い式になってしまいますし、ぶっちゃけVBAのSplit関数を使って配列にしてから出力する方が早いし楽です。

Sub 文字列分割()
    Dim strArray As Variant
    strArray = Split(Cells(1, 1).Value, "|")
    
    For i = 0 To UBound(strArray)
        Cells(1, 2 + i).Value = strArray(i)
    Next i
End Sub

どっちを使うのかはケースバイケースですな。