文字列を位置で分割する

  1. Home
  2. Excel
  3. VBA
  4. Excel VBA Tips

「123,田中,tanaka」というデータを"123"と"田中"と"tanaka"に分割するのは、Split関数で一発です。詳しくは「Split関数で文字列を区切る」をご覧ください。

では「ABC12345DE678」という固定長のデータを"ABC"と"12345"と"DE"と"678"に分割するには、どうしたらいいでしょう。今回は、そういうお話です。

「区切り位置」機能でやってみる

たぶん、多くの人がこう考えるでしょうね。Excelには「区切り位置」という機能があります。それを使えば、手動だったら簡単です。

まずは簡単に、元の文字列がセルA1に入っていて、それをB列から右に分割してみました。さて、この操作をマクロ記録すると、次のようなコードが記録されます。

Sub Macro1()
    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(8, 1), Array(10, 1)), _
        TrailingMinusNumbers:=True
End Sub

TextToColumnsメソッドを使うと分かりました。ただ問題は、

FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(8, 1), Array(10, 1))

です。これは、いったい何を言っているのでしょう。

まずは左端にArray関数がありますから、これは配列を作っています。しかし、その配列の各要素が、また配列になっています。このように、配列の要素が配列である配列のことを「ジャグ配列」と呼びます。1次元配列でさえイメージするのが難しい人が、ジャグ配列をイメージするのは無理でしょう。さて、要素に指定している配列は、次の意味です。

Array(0, 1) → 区切る位置の先頭が0文字目＋形式は"標準"
Array(3, 1) → 区切る位置の先頭が3文字目＋形式は"標準"
Array(8, 1) → 区切る位置の先頭が8文字目＋形式は"標準"
Array(10, 1) → 区切る位置の先頭が10文字目＋形式は"標準"

この配列の、2番目の要素には、次の定数または数値を指定します。

定数 値 形式
xlGeneralFormat 1 標準
xlTextFormat 2 文字列
xlMDYFormat 3 MDY (月日年) 形式の日付
xlDMYFormat 4 DMY (日月年) 形式の日付
xlYMDFormat 5 YMD (年月日) 形式の日付
xlMYDFormat 6 MYD (月年日) 形式の日付
xlDYMFormat 7 DYM (日年月) 形式の日付
xlYDMFormat 8 YDM (年日月) 形式の日付
xlEMDFormat 10 EMD (台湾年月日) 形式の日付
xlSkipColumn 9 スキップ列

定数xlEMDFormatは、簡易字中国語サポートがインストールされている環境で使用可能です。

ここで指定する形式は、ウィザードの3/3で指定する「列のデータ形式」です。

ちなみに、記録されたコードの最後

TrailingMinusNumbers:=True

は、ウィザード3/3画面にある[詳細]ボタンをクリックして指定できる[負の数はマイナスを表示する]オプションです。

この記録されたコードを、そのまま使ったり、これを応用してマクロを作成することは、お勧めしません。理由は次の3つです。

  1. そもそもジャグ配列なんて難しいでしょ、知ってました？
  2. これを複雑な実務のデータに応用する自信がありますか？
  3. 元データと出力先が、どちらもワークシート内に限られる

まぁ、ほとんどの人は1.と2.で断念するでしょうけど、問題は3.です。この「区切り位置」機能は、セル内のデータを分割して、指定したセルに出力する機能です。元データがセルに入っていなければなりませんし、出力先もセルに限定されます。テキストファイルを読み込んで、それを分割して…という使い方には不向きです。

Mid関数で抜き出す

そもそも、文字列の中で、指定した位置から、指定した文字数分の文字を抜き出すには、Mid関数を使えます。だから、マクロ記録した「区切り位置」機能なんか使わずに、Mid関数を使って区切ればいいんです。たとえば、今回のケースでしたら、次のように考えられます。

これを、まずはベタに書いてみましょう。

Sub Macro2()
    Range("B1") = Mid(Range("A1"), 1, 3)
    Range("C1") = Mid(Range("A1"), 4, 5)
    Range("D1") = Mid(Range("A1"), 9, 2)
    Range("E1") = Mid(Range("A1"), 11, 3)
End Sub

ここから法則性を探します。このように区切るためには、

  1. それぞれの項目は何文字目から始まるか
  2. それぞれの項目は何文字の長さか

の、いずれかが情報として必要です。ここでは、1.の「それぞれの項目は何文字目から始まるか」が情報として与えられたとします。

  • 項目1 → 1文字目から
  • 項目2 → 4文字目から
  • 項目3 → 9文字目から
  • 項目4 → 11文字目から

この4つの数値「1, 4, 9, 11」から、先のMid関数を作れればいいわけです。まず、Mid関数の第2引数

項目1：Mid(文字列, 1, 3)
項目2：Mid(文字列, 4, 5)
項目3：Mid(文字列, 9, 2)
項目4：Mid(文字列, 11, 3)

は、いいですね。分かっています。では、3番目の引数は、どういう関係になっているのでしょうか。

  • 項目1：3 → 「項目2の開始位置 - 項目1の開始位置」=「4 - 1」
  • 項目2：5 → 「項目3の開始位置 - 項目2の開始位置」=「9 - 4」
  • 項目3：2 → 「項目4の開始位置 - 項目3の開始位置」=「11 - 9」
  • 項目4：3 → 上と同じ計算で求めることはできない

つまり、最後の項目を除いて、それ以外は「次の開始位置から、自分の開始位置を引いた数」が、文字数になります。では、最後の項目の文字数は、どうやって求めればいいのでしょうか。上には"できない"と書きましたが、まぁ、ここまで抜き出した文字数を合計して、元の文字数から差し引けば計算できますけど、そんなの面倒くさいです。実は、超簡単な方法があります。

最後の項目「11文字目から3文字分」というのは「11文字目から後ろ全部」とも言えます。Mid関数は、第3引数の"文字数"を省略すると、後ろ全部を抜き出します。したがって、最後の項目は

項目1：Mid(文字列, 1, 3)
項目2：Mid(文字列, 4, 5)
項目3：Mid(文字列, 9, 2)
項目4：Mid(文字列, 11)

で、いいんです。ここまでを、簡単なマクロで確認してみましょう。

Sub Macro3()
    Dim i As Long, Pos(3) As Long, Result As String
    Pos(0) = 1
    Pos(1) = 4
    Pos(2) = 9
    Pos(3) = 11
    For i = 0 To UBound(Pos) - 1
        Result = Result & Mid(Range("A1"), Pos(i), Pos(i + 1) - Pos(i)) & vbCrLf
    Next i
    Result = Result & Mid(Range("A1"), Pos(i))
    MsgBox Result
End Sub

うまくいきました。最後にやっている変数iの使い方は、ちょっと高度です。さて、こうした作業を実務で行うのでしたら、この処理はFunctionにしておくといいですね。

Sub Macro4()
    Dim A As Variant, i As Long
    A = FixedWidth(Range("A1"), Array(1, 4, 9, 11))
    For i = 0 To UBound(A)
        MsgBox A(i)
    Next i
End Sub

Function FixedWidth(A As String, Pos)
    Dim i As Long
    ReDim Result(UBound(Pos))
    For i = 0 To UBound(Pos) - 1
        Result(i) = Mid(A, Pos(i), Pos(i + 1) - Pos(i))
    Next i
    Result(i) = Mid(A, Pos(i))
    FixedWidth = Result
End Function

Functionには、2つの引数を指定します。ひとつめは、分割する前の「固定長文字列」です。ふたつめの引数には、何文字目から区切るかを示す数値を配列形式で指定します。ここではArray関数を使っています。分割した結果は、Split関数のように配列で返します。Split関数と同じように、バリアント型で受ければいいでしょう。

最後にオマケで、固定長データのテキストファイルを読み込んで、セルに分割代入してみましょう。ここでは、次のようなデータとします。とりあえず100行作りました。

次の位置で区切るものとします。

Sub Macro5()
    Dim buf As String, i As Long, A
    Open "C:\Work\Sample.txt" For Input As #1
        Do Until EOF(1)
            Line Input #1, buf
            A = FixedWidth(buf, Array(1, 11, 17, 21, 22))
            i = i + 1
            Cells(i, 1).Resize(, UBound(A) + 1) = A
        Loop
    Close #1
End Sub

Function FixedWidthは、上に書いたやつです。実行結果は以下のとおり。

日付が「yyyy/m/d」になっているのは、Excelの(というかセルの)仕様ですね。お好みの表示形式を設定すればいいでしょう。