EXCEL VBA 文字列を分ける・区切り文字で別々のセルに分割・文字列を順番に区切る（TextToColumns）

2022年2月12日

EXCEL VBA 文字列を分ける・区切り文字で別々のセルに分割・文字列を複数のセルに分割・氏名・住所を分割（TextToColumnsメゾット）

●はじめに

今回説明するのは、Range.TextToColumns メソッドの利用方法を説明します。Range.TextToColumns メソッドでは、文字列データにスペースやカンマで区切られている文字列データに対して、別々のセルに転記する事ができます。また、1行に連続的に文字列データが登録されている場合に文字列の何番目～何番目など、先頭文字列から順番に区切り、区切った文字列ごとに、別々のセルへ転記する事ができます。同じ形式の統一したデータに対して、文字列を分割する際や連続の文字列を任意の場所で区切る場合には、とても便利だと思います。それでは、サンプルプログラムを交えて順番に説明いたします。

●【Range.TextToColumns メソッド (Excel)、下記を参照して下さい。（Microsoft社　様）】
https://docs.microsoft.com/ja-jp/office/vba/api/excel.range.texttocolumns

●【EXCEL VBA 文字列を分割・指定文字で区切る・カンマ・区切り・配列格納（Split関数）】については、下記を参照して下さい】

EXCEL VBA 文字列を分割・指定文字で区切る・カンマ区切り・配列格納　（Split関数）

●書式の説明

●　TextToColumnsメゾットを利用するには、下記の通りに設定を行います。

Rangeオブジェクト.TextToColumns ( Destination, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers )

定数	必須・オプション	データ型	内容・説明
Destination	省略可能	Variant	Range オブジェクトでの結果の表示先を指定。表示先に指定したセル範囲が複数セルの場合は、左上端のセルが対象となります。なお、省略すると元データが入力されているセルが起点となります。
DataType	省略可能	XlTextParsingType	複数セルに分割する形式を XlTextParsingType の定数を指定します。 ※詳細は下記を参照して下さい。
TextQualifier	省略可能	XlTextQualifier	文字列に使用する引用符を XlTextQualifier の定数で指定します。 ※詳細は下記を参照して下さい。
ConsecutiveDelimiter	省略可能	Variant	True を指定すると、区切り文字を 1 つの区切り文字として認識します。既定値は False。
Tab	省略可能	Variant	True を指定すると、引数 DataType は xlDelimited で区切り文字がタブ文字です。既定値は False。
Semicolon	省略可能	Variant	True を指定すると、引数 DataType は xlDelimited で区切り文字がセミコロン【；】です。既定値は False。
Comma	省略可能	Variant	True を指定すると、引数 DataType は xlDelimited で区切り文字がコンマ【,】です。既定値は False。
Space	省略可能	Variant	True を指定すると、引数 DataType は xlDelimited で区切り文字がスペースです。既定値は False 。
Other	省略可能	Variant	True を指定すると、引数 DataType は xlDelimited で区切り文字が引数 OtherChar で指定した文字です。既定値は False。
OtherChar	省略可能	Variant	引数 Other が True の場合の区切り文字を指定します。複数の文字を指定したときは、先頭の文字だけが区切り文字として使われ、残りの文字は無視されます。
FieldInfo	省略可能	Variant	区切り後の列のデータ形式に関する情報を持つ配列を指定します。解釈は、DataType の値によって異なります。

●　XlTextParsingTypeの定数

定数	値	内容・説明
xlDelimited	1	区切り文字形式（既定値）
xlFixedWidth	2	固定長形式

●　XlTextQualifierの定数

定数	値	内容・説明
xlTextQualifierDoubleQuote	1	ダブルクォーテーション（二重引用符）（既定値）
xlTextQualifierSingleQuote	2	シングルクォーテーション（一重引用符）
xlTextQualifierNone	-4142	引用符なし

【使用例①】
●　TextToColumnsメゾットを使って、氏名を分割（氏・名）してみましょう。

【サンプルプログラム①】

'
'
Sub TextToColumns00() '氏名を分割します。

    Range("A2:A11").TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, Space:=True
    'セルA2～A11の範囲の文字列に対して、スペースで分割してセルB2を起点に表示させます。

End Sub
'

Sub TextToColumns00() '氏名を分割します。

Range("A2:A11").TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, Space:=True

'セルA2～A11の範囲の文字列に対して、スペースで分割してセルB2を起点に表示させます。

End Sub

【使用例②】
●　TextToColumnsメゾットを使って、文字列を指定した箇所で区切ります。（携帯番号を別々のセルに3分割します）

【サンプルプログラム②】

'
'
Sub TextToColumns01() '電話番号（区切り位置）を分割します。

    Range("B2:B11").TextToColumns Destination:=Range("D2"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(3, 2), Array(7, 2))
    'セルB2～B11の範囲の文字列に対して、設定した区切り位置で分割します。

End Sub
'

Sub TextToColumns01() '電話番号（区切り位置）を分割します。

Range("B2:B11").TextToColumns Destination:=Range("D2"), DataType:=xlFixedWidth, _

FieldInfo:=Array(Array(0, 2), Array(3, 2), Array(7, 2))

'セルB2～B11の範囲の文字列に対して、設定した区切り位置で分割します。

End Sub

※分割位置の説明は、下記の通りになります。

D列　：　Array(0, 2) → 区切る位置の先頭が１文字目で表示形式は【文字列形式：２】（3文字）
E列　：　Array(3, 2) → 区切る位置の先頭が４文字目で表示形式は【文字列形式：２】（4文字）
F列　：　Array(7, 2) → 区切る位置の先頭が８文字目で表示形式は【文字列形式：２】（4文字）

●　　Array(何文字目,文字列形式）

※何文字目は、1文字目が０から始まります。
※表示形式については、↓【XlColumnDataTypeの定数】を参照してください。　

●　XlColumnDataTypeの定数

定数	値	内容・説明
xlGeneralFormat	1	一般形式（標準）
xlTextFormat	2	文字列形式
xlMDYFormat	3	MDY （月日年）日付形式
xlDMYFormat	4	DMY 日付形式
xlYMDFormat	5	YMD 日付形式
xlMYDFormat	6	MYD 日付形式
xlDYMFormat	7	DYM 日付形式
xlYDMFormat	8	YDM 日付形式
xlSkipColumn	9	スキップ列（転記しない）
xlEMDFormat	10	EMD日付形式

【注意】TextToColumnsメソッドで上書き確認を非表示にする場合は、【Application.DisplayAlerts = False　】にする事で、下記のメッセージによる【置き換えメッセージ一】を防止（非表示）する事が出来ます。

【使用例】

　Application.DisplayAlerts = False ‘エラーメッセージ非表示

　Range(“A2:A11”).TextToColumns Destination:=Range(“B2”), DataType:=xlDelimited, Space:=True

Application.DisplayAlerts = True　’エラーメッセージを表示

EXCEL VBA 文字列を分ける・CSVファイル（カンマ区切）を別々のセルに分割・文字列を複数のセルに分割・氏名・住所を分割（TextToColumnsメゾット）

●プログラム説明　(サンプル①)

下記のサンプルプログラムは、CSVファイルなどのカンマ区切りのデータに対して、別々のセルに分割してデータを置換えるサンプルプログラムです。

【プログラムの流れ】

①　A列の最終行を取得します。
②　A列に登録されているCSVデータ（カンマ区切り）を別々のセルに分割して置き換えます。

'
'
Sub TextToColumns02() 'CSVデータ（カンマ区切り）のデータを別々のセルに分割します。

    Dim I, lRow As Long
    
    lRow = Cells(Rows.Count, "A").End(xlUp).Row 'A列の最終行を取得します。

    Range("A1:A" & lRow).TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, Comma:=True
    'セルのA列にCSVファイルが登録されているので、このデータをセルごとに分割しデータを置換えるます。

End Sub
'

Sub TextToColumns02() 'CSVデータ（カンマ区切り）のデータを別々のセルに分割します。

Dim I, lRow As Long

lRow = Cells(Rows.Count, "A").End(xlUp).Row 'A列の最終行を取得します。

Range("A1:A" & lRow).TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, Comma:=True

'セルのA列にCSVファイルが登録されているので、このデータをセルごとに分割しデータを置換えるます。

End Sub

●実行前～実行後　※プログラム実行後、A列のCSVデータがカンマ区切りごと別々セルに分割されデータが置き換わりました。
（画面クリックして拡大）

EXCEL VBA 文字列を分ける・CSVファイル（カンマ区切）を別々のセルに分割・文字列を複数のセルに分割・氏名・住所を分割し、条件に一致するデータを別シートに転記します。

●プログラム説明　(サンプル②)

下記のサンプルプログラムは、上記①の応用編になります。サンプルプログラム①では、カンマ区切りの全てのCSVデータを別々のセルに分割してデータを置換えましたが、今回のサンプルプログラムでは、同一シートでは無くセル毎に分割したデータを別々のセルに分割してその結果を別シートに転記するサンプルプログラムです。

【プログラムの流れ】
①　ワークシート「DATA」A列の最終行を取得します。（CSVデータのデータ数を把握します。）
②　ワークシート「DATA」A列に登録されているデータをカンマ区切りごとに別々セルへC列を起点に転記されます。
③　ワークシート「DATA」C列を起点にデータの範囲を取得し、選択範囲をコピーします。
④　ワークシート「社員一覧」へコピーした選択範囲を貼り付けます。
⑤　ワークシート「DATA」のC列を起点のデータをクリアーします。

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。

●　TextToColumns03（サンプルファイル）

'
'
'
Sub TextToColumns03() 'CSVデータ（カンマ区切り）のデータを別シートに転記します。


    Dim ws01, ws02 As Worksheet
    Dim lRow As Long
    
    Set ws01 = Worksheets("DATA")
    Set ws02 = Worksheets("社員一覧")
    
    With ws01
            lRow = .Cells(Rows.Count, "A").End(xlUp).Row 'シート「DATA」A列の最終行を取得します。
            .Range("A1:A" & lRow).TextToColumns Destination:=.Range("C1"), DataType:=xlDelimited, Comma:=True
            'シート「DATA」A列に登録されているデータを分割してC列より別々のセルに転記します。
            .Range("C1").CurrentRegion.Copy 'セルC1を起点にセル範囲を取得し、選択範囲をコピーします。
        ws02.Range("A2").PasteSpecial xlPasteValues 'コピーした範囲をシート「社員一覧」へ貼り付けます。。
            .Range("C1").CurrentRegion.ClearContents 'シート「DATA」C1を起点にセル範囲を取得して文字列をクリアーします。
    End With
    
End Sub
'

Sub TextToColumns03() 'CSVデータ（カンマ区切り）のデータを別シートに転記します。

Dim ws01, ws02 As Worksheet

Dim lRow As Long

Set ws01 = Worksheets("DATA")

Set ws02 = Worksheets("社員一覧")

With ws01

lRow = .Cells(Rows.Count, "A").End(xlUp).Row 'シート「DATA」A列の最終行を取得します。

.Range("A1:A" & lRow).TextToColumns Destination:=.Range("C1"), DataType:=xlDelimited, Comma:=True

'シート「DATA」A列に登録されているデータを分割してC列より別々のセルに転記します。

.Range("C1").CurrentRegion.Copy 'セルC1を起点にセル範囲を取得し、選択範囲をコピーします。

ws02.Range("A2").PasteSpecial xlPasteValues 'コピーした範囲をシート「社員一覧」へ貼り付けます。。

.Range("C1").CurrentRegion.ClearContents 'シート「DATA」C1を起点にセル範囲を取得して文字列をクリアーします。

End With

End Sub

●実行前～実行後　※プログラム実行後、シート「DATA」にCSVデータが登録されています。このデータをカンマ区切りごとに、別々のセルに転記して、結果を別シート「社員一覧」へ転記します。
（画面クリックして拡大）

EXCEL VBA 文字列を分ける・テキストファイル（固定長）を指定した文字列位置で分割・文字列を複数のセルに分割し、別シートに転記します。

●プログラム説明　(サンプル③)

下記のサンプルプログラムでは、テキストファイル（固定長）のファイルに対して、下表のデータレコードの文字列間隔ごとに別々のセルに転記して、別シート（取引データ）へ転記するサンプルプログラムです。全銀データなど固定長のファイルをセル毎に区切る時に便利だと思います。それでは、サンプルプログラムを交えて順番に説明します。

【プログラムの流れ】
①　ワークシート「DATA」A列の最終行を取得します。（テキスト固定長データの最終行を取得します。）
②　ワークシート「DATA」A列に登録されているテキスト（固定長）データを分割してC列より別々のセルに転記します。
③　ワークシート「DATA」セル「C1」を起点にセル範囲を取得し、選択範囲をコピーします。
④　ワークシート「取引データ」へデータを貼り付けます。
⑤　ワークシート「DATA」セル「C１」を起点にセル範囲を取得し、文字列をクリアーします。（貼り付けたデータをクリアーします。）

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。

●　TextToColumns04（サンプルファイル）

'
'
'
Sub TextToColumns04() 'テキストデータ(固定長）のデータを別シートに転記します。


    Dim ws01, ws02 As Worksheet
    Dim lRow As Long
    
    Set ws01 = Worksheets("DATA")
    Set ws02 = Worksheets("取引データ")
    
    With ws01
            lRow = .Cells(Rows.Count, "A").End(xlUp).Row 'シート「DATA」A列の最終行を取得します。
            .Range("A1:A" & lRow).TextToColumns Destination:=Range("C1"), DataType:=xlFixedWidth, _
                  FieldInfo:=Array(Array(0, 2), Array(1, 2), Array(3, 2), Array(11, 2), Array(15, 2), Array(28, 2), Array(31, 2), Array(32, 2), Array(39, 2), Array(51, 2))
                            
            'シート「DATA」A列に登録されているテキストデータ（固定長）を指定したレコードごと分割してC列より別々のセルに転記します。
          
            .Range("C1").CurrentRegion.Copy 'セルC1を起点にセル範囲を取得し、選択範囲をコピーします。
        ws02.Range("A2").PasteSpecial xlPasteValues 'コピーした範囲をシート「社員一覧」へ貼り付けます。。
            .Range("C1").CurrentRegion.ClearContents 'シート「DATA」C1を起点にセル範囲を取得して文字列をクリアーします。
    End With
    
End Sub
'

Sub TextToColumns04() 'テキストデータ(固定長）のデータを別シートに転記します。

Dim ws01, ws02 As Worksheet

Dim lRow As Long

Set ws01 = Worksheets("DATA")

Set ws02 = Worksheets("取引データ")

With ws01

lRow = .Cells(Rows.Count, "A").End(xlUp).Row 'シート「DATA」A列の最終行を取得します。

.Range("A1:A" & lRow).TextToColumns Destination:=Range("C1"), DataType:=xlFixedWidth, _

FieldInfo:=Array(Array(0, 2), Array(1, 2), Array(3, 2), Array(11, 2), Array(15, 2), Array(28, 2), Array(31, 2), Array(32, 2), Array(39, 2), Array(51, 2))

'シート「DATA」A列に登録されているテキストデータ（固定長）を指定したレコードごと分割してC列より別々のセルに転記します。

.Range("C1").CurrentRegion.Copy 'セルC1を起点にセル範囲を取得し、選択範囲をコピーします。

ws02.Range("A2").PasteSpecial xlPasteValues 'コピーした範囲をシート「社員一覧」へ貼り付けます。。

.Range("C1").CurrentRegion.ClearContents 'シート「DATA」C1を起点にセル範囲を取得して文字列をクリアーします。

End With

End Sub

●実行前～実行後　※プログラム実行後、シート「DATA」A列に登録されているテキスト（固定長）データを指定したデータレコードごとに分割（文字数）し別シート「取引データ」へ転記されました。
（画面クリックして拡大）