【ExcelVBA・マクロ】TextToColumnsメソッドの使い方｜1列のデータを区切り文字で複数列に分割する方法【コピペOK】

「山田太郎,営業部,03-1234-5678」のように、1つのセルにカンマや空白で区切られたデータが入っていて、それぞれを別々の列に分けたい……という場面はよくあります。手作業なら「区切り位置」機能を使いますが、毎回同じ形式のデータであればVBAで自動化してしまうのが効率的です。

この記事では、TextToColumnsメソッドを使って1列のデータを複数列に分割する基本的な使い方から、固定長データの分割、分割時に数値や日付が意図せず変換されてしまう問題への対処法まで解説します。

TextToColumnsとは
基本の構文
カンマ区切りのデータを分割する
スペース区切り・タブ区切りに対応する
固定長データを分割する
分割後の型が変わってしまう問題への対処
よくあるエラーと対処法
まとめ

TextToColumnsとは

TextToColumnsは、Excelの「データ」タブにある「区切り位置」機能をVBAから実行するためのメソッドです。Rangeオブジェクトに対して呼び出し、カンマ・スペース・タブなどの区切り文字、または文字数で指定した固定長の位置で、1列のデータを複数列に分割できます。

CSVからコピーしたデータや、他システムから出力された「区切り文字入りの1列データ」を整形する処理でよく使われます。

基本の構文

Range.TextToColumns( _
    Destination:=Range("A1"), _
    DataType:=xlDelimited, _
    Comma:=True, _
    Space:=False, _
    Other:=False, _
    FieldInfo:=Array(Array(1, xlGeneralFormat), Array(2, xlGeneralFormat)))

主な引数は以下のとおりです。

Destination：分割結果を書き込む先頭セル。省略すると分割元のセル自体が上書きされます
DataType：xlDelimited（区切り文字で分割）かxlFixedWidth（文字数で分割）を指定
Comma・Space・Tab・Semicolon：区切り文字として使うかどうかをTrue/Falseで指定
FieldInfo：分割後の各列のデータ形式を配列で指定（省略可）

カンマ区切りのデータを分割する

A列に「氏名,部署,電話番号」の形式で入力されたデータを、B列以降に分割する例です。

Sub SplitByComma()
    Dim targetRange As Range
    Dim lastRow As Long

    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Set targetRange = Range("A1:A" & lastRow)

    targetRange.TextToColumns _
        Destination:=Range("B1"), _
        DataType:=xlDelimited, _
        Comma:=True
End Sub

DestinationをA1ではなくB1に指定することで、元データをA列に残したまま、分割結果をB列以降に書き出せます。A1を指定すると元データ自体が上書きされる点に注意してください。

スペース区切り・タブ区切りに対応する

区切り文字はCommaだけでなく、複数を同時にTrueにすることも可能です。

Sub SplitBySpaceOrTab()
    Range("A1:A10").TextToColumns _
        Destination:=Range("C1"), _
        DataType:=xlDelimited, _
        Tab:=True, _
        Space:=True
End Sub

タブ区切りとスペース区切りの両方をTrueにしておくと、「タブとスペースのどちらで区切られているか分からないデータ」でもまとめて対応できます。カンマ・タブ・スペース以外の記号（「｜」や「/」など）で区切りたい場合は、Other:=TrueとOtherChar:="｜"のように指定します。

固定長データを分割する

郵便番号や社員コードのように、区切り文字ではなく「先頭から何文字目まで」で切り分けたいデータにはxlFixedWidthを使います。

Sub SplitFixedWidth()
    Range("A1:A10").TextToColumns _
        Destination:=Range("D1"), _
        DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, xlGeneralFormat), Array(3, xlGeneralFormat))
End Sub

FieldInfoの各配列の1つ目の数値が「区切り位置（何文字目から次のフィールドが始まるか）」を表します。上記の例では0文字目と3文字目で区切っているため、「先頭3文字」と「4文字目以降」の2列に分割されます。

分割後の型が変わってしまう問題への対処

TextToColumnsは分割後の値を自動的に数値や日付として解釈するため、「001」という文字列が「1」になったり、「3-10」という文字列が日付として扱われたりすることがあります。これを防ぐには、FieldInfoで列ごとの形式をxlTextFormatに指定します。

Sub SplitKeepAsText()
    Range("A1:A10").TextToColumns _
        Destination:=Range("E1"), _
        DataType:=xlDelimited, _
        Comma:=True, _
        FieldInfo:=Array(Array(1, xlTextFormat), Array(2, xlTextFormat))
End Sub

FieldInfoの配列は「列番号（1始まり）」と「データ形式」のセットです。分割対象の列すべてにxlTextFormatを指定しておけば、0埋めのコードや「1-2」のような文字列が数値・日付に化けるのを防げます。

よくあるエラーと対処法

「型が一致しません」エラーになる：FieldInfoの配列の書き方（Array(列番号, 形式)の入れ子構造）が崩れている場合に発生します。列数分のArrayが正しく入っているか確認してください
分割結果が思った位置に出ない：Destinationを分割元と同じ列にしていないか確認してください。元データを残したい場合は必ず別の列を指定します
複数行を一括処理したのに1行しか分割されない：Rangeの指定が1セルだけになっていないか確認してください。Cells(Rows.Count, "A").End(xlUp).Rowで最終行を取得し、範囲全体を対象にします

まとめ

TextToColumnsメソッドを使うことで、区切り位置機能をVBAから自動実行し、1列のデータを複数列に分割できます。

DataTypeで区切り文字（xlDelimited）か固定長（xlFixedWidth）かを指定する
Destinationを分割元と別の列にすることで、元データを残したまま分割できる
FieldInfoにxlTextFormatを指定すると、0埋めコードなどが数値・日付に変換されるのを防げる

CSV貼り付けデータの整形や、他システムからの出力データを表形式に整える処理を自動化する際にぜひ活用してみてください。