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

 

 

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

● XlTextParsingTypeの定数

定数内容・説明
xlDelimited1区切り文字形式
(既定値)
xlFixedWidth2固定長形式

 

● XlTextQualifierの定数

定数内容・説明
xlTextQualifierDoubleQuote1ダブルクォーテーション(二重引用符)(既定値)
xlTextQualifierSingleQuote2シングルクォーテーション(一重引用符)
xlTextQualifierNone -4142引用符なし

 

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

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

 

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

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

 

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

D列 : Array(0, 2) → 区切る位置の先頭が1文字目で表示形式は【文字列形式:】(3文字)
E列 : Array(3, 2) → 区切る位置の先頭が4文字目で表示形式は【文字列形式:】(4文字)
F列 : Array(7, 2) → 区切る位置の先頭が8文字目で表示形式は【文字列形式:】(4文字)

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

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

● XlColumnDataTypeの定数

定数内容・説明
xlGeneralFormat1一般形式(標準)
xlTextFormat2文字列形式
xlMDYFormat3MDY (月日年)日付形式
xlDMYFormat4DMY 日付形式
xlYMDFormat5YMD 日付形式
xlMYDFormat6MYD 日付形式
xlDYMFormat7DYM 日付形式
xlYDMFormat8YDM 日付形式
xlSkipColumn9スキップ列(転記しない)
xlEMDFormat10EMD日付形式

 

【注意】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データ(カンマ区切り)を別々のセルに分割して置き換えます。

 

 

 

 

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

 

 

 

 

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

 

 

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

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

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

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

● TextToColumns03(サンプルファイル)

 

 

 

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

 

 

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

 

 

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

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

 

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

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

● TextToColumns04(サンプルファイル)

 

 

 

 

 

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

 

 

最後まで、ご覧いただきまして誠に有難うございました。
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。