【VBA】区切り文字を含む単一セル内のデータを複数セルに分割する

これで楽に！？VBA活用

2022.10.31

記事内に広告が含まれています。

こんにちは、Ryoです。
1つのセル内にカンマやセミコロン、スペースなどで区切られた一つデータや文字列が入力されていて、そのデータを他セルに分割して複数のセルに入力したい場合はTextToColumnsメソッドを利用することで対処できますので、今回はその内容について書いていきます。

1.サンプル概要
2.サンプルコード
3.まとめ

1.サンプル概要

サンプルとして「カンマ区切り」「セミコロン区切り」「スペース区切り」「任意文字列区切り」の4つを準備しています。

それぞれ指定した区切り文字でデータを区切り、セルC列以降に分割して入力します。

≪Sample1:カンマ区切り実行結果≫

≪Sample2:セミコロン区切り実行結果≫

≪Sample3：スペース区切り実行結果≫

サンプルではスペースは半角／全角を混在させていますが、半角／全角を区別しませんので問題なく処理できます。

≪Sample4：任意文字区切り実行結果≫

サンプルでは区切り文字として”_”（アンダーバー）を指定しています。

動作状態としては以下の通りです。

以上がサンプル概要になります。

2.サンプルコード

◆Sample1:カンマ区切り

Sub Sample1()
'＊＊＊カンマ区切りでセルへ分割＊＊＊

 '実行都度確認メッセージが表示されるので非表示化しておく
 Application.DisplayAlerts = False
 
 'セルA2～A4の文字列をカンマ区切りでセルC列以降へ分割して入力
 Range("A2:A4").TextToColumns Destination:=Range("C2:C4"), _
 DataType:=xlDelimited, Comma:=True
 
 '※　DataType:=xlDelimited　→　区切り文字で区切る
 ' 　 Comma:＝True　→　カンマで区切る
 
 '確認（警告）メッセージの非表示化を解除
 Application.DisplayAlerts = True

End Sub

◆Sample2:セミコロン区切り

Sub Sample2()
'＊＊＊セミコロン区切りでセルへ分割＊＊＊

 '確認(警告）メッセージ非表示化
 Application.DisplayAlerts = False
 
 'セルA7～A9の文字列をセミコロン区切りでセルC列以降へ分割入力
 Range("A7:A9").TextToColumns Destination:=Range("C7:C9"), _
 DataType:=xlDelimited, Semicolon:=True
 
 '※　DataType:=xlDelimited　→　区切り文字で区切る
 '　　Semicolon:＝True　→　セミコロンで区切る
 
 '確認（警告）メッセージの非表示化を解除
 Application.DisplayAlerts = True

End Sub

◆Sample3：スペース区切り

Sub Sample3()
'＊＊＊スペース区切りでセルへ分割＊＊＊

 '確認（警告)メッセージ非表示化
 Application.DisplayAlerts = False
 
 'セルA12～A14の文字列をスペース区切りでセルC列以降へ分割入力
 Range("A12:A14").TextToColumns Destination:=Range("C12:C14"), _
 DataType:=xlDelimited, Space:=True
 
 '※　DataType:=xlDelimited　→　区切り文字で区切る
 '　　Space:＝True　→　スペースで区切る（全角／半角は区別しない）
 
 '確認（警告）メッセージの非表示化を解除
 Application.DisplayAlerts = True

End Sub

◆Sample4：任意文字区切り

Sub Sample4()
'＊＊＊任意文字区切りでセルへ分割＊＊＊

 '確認（警告)メッセージ非表示化
 Application.DisplayAlerts = False
 
 'セルA12～A14の文字列を任意文字区切りでセルC列以降へ分割入力
 Range("A17:A19").TextToColumns Destination:=Range("C17:C19"), _
 DataType:=xlDelimited, Other:=True, OtherChar:="_"
 
 '※　DataType:=xlDelimited　→　区切り文字で区切る
 '　　Other:＝True　→　任意文字で区切る指定
 '　　OtherChar:="任意文字"　→　区切り文字とする文字を指定
 '　　　　　　　　　　　　　　　（サンプルでは ”_”）
 
 '確認（警告）メッセージの非表示化を解除
 Application.DisplayAlerts = True

End Sub

このようにセル内のデータを複数のセルに分割するにはTextToColumnsメソッドを利用します。

構文：TextToColumns(Destination, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, OtherChar, FieldInfo, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers)

TextToColumnsメソッドの主な引数としては以下の通りです。

引数	内容
Destination	結果表示先のセルを指定
DataType	区切るデータ形式の指定。xlDelimitedは区切り文字によって区切り、xlFixedWidthは固定長のデータとして区切る。
TextQualifier	文字列の引用符に一重引用符（xlTextQualifierSingleQuote）、二重引用符（xlTextQualifierDoubleQuote）、引用符を使用しない（xlTextQualifierNone)を指定。
ConsecutiveDelimiter	True指定で連続した区切り文字を1つの区切り文字として認識。
Tab	True指定で区切り文字にタブ文字を指定
Comma	True指定で区切り文字にカンマを指定
Other	True指定で区切り文字を引数OtherCharに指定した文字とする
OtherChar	引数OtherがTrueの時に区切り文字として使用する文字を指定

カンマ／セミコロン／スペース／任意文字それぞれの区切る処理についてはサンプルコード内のコメント通りで「DataType:=xlDelimited→区切り文字で区切る」、「Comma/Semicolon/Space/Other:＝True→指定文字で区切る」、「Otherを指定した場合に限りOtherChar:=”任意文字”→区切り文字とする文字の指定をする」ことで処理できます。

コード内で使用しているApplication.DisplayAlerts については、指定した入力先のセルが空欄であっても下の画像の通り確認（警告）メッセージが表示されますので非表示とするための処理になります。