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

 

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

 

 

 ●はじめに

今回説明するのは、Split関数の利用方法を説明いたします。Split関数は、1つに纏まった文字列を文字列内の区切り文字で文字列を分割する事ができます。特定の文字を指定して分割する事もできるので、カンマ区切りのCSVファイルなどを項目ごとに区切ってEXCELシートに転記する事が出来ます。それでは、サンプルプログラムを交えて順番に説明いたします。

 

●【Split関数については、下記を参照して下さい】:参考(Microsoft社 様)
https://docs.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/split-function

 

 

 ●書式の説明

● Split関数を利用するには、下記の通りに設定を行います。

【構文】

● Split(文字列,区切り文字,最大分割数,比較モード)

引数必須/省略説明(内容)
文字列(Expression)必須文字列を指定します。(区切り文字も含めます)
区切り文字(Eelimiter)省略可能分割位置を表す区切り文字を指定します。しょう
最大分割数(Limit)省略可能Split関数が返す配列の要素数を指定します。通常は省略します。
比較モード(Compare)省略可能区切り文字を識別するときの比較モードを指定します。
vbBinaryCompare : バイナリ モードで比較を行います。
vbTextCompare : テキスト モードで比較を行います。
※バイナリ モードでは、全角半角、大文字小文字が区別される。
  テキストモードでは、全角半角、大文字小文字が区別されない。

【使用例】:Split関数のサンプル

【プログラム】

 

【注意点】

Split関数が返す配列を要素数を指定した配列変数に格納する事ができません。Split関数が返す配列は、要素数を指定しない動的配列に格納する事が出来ます。
(例)
Dim Buf(2) as  Variant  ・・・・・ Split関数では配列へ格納できない。(要素数を指定は、×)
Dim Buf() as  Variant  ・・・・・・ 配列へ格納できる。(動的配列で〇)

 

 

 

 

EXCEL VBA 一つの文字列を分割して別々の列に転記します。(Split関数)

 

 

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

下記のサンプルプログラムは、一つの文字列データに対して文字列を分割して別々の列(セル)に転記するサンプルプログラムです。A列にはカンマ区切りの文字列データが登録されています。(文字列データ:社員番号、氏名、メールアドレス、出身地)この文字列データをC列~F列の各項目へ転記するサンプルプログラムです。

 

【プログラムの流れ】
① A列の最終行を取得します。
② 2行目からA列の最終行まで繰り返します。
③ Split関数を使ってカンマ区切りの文字列を分割して配列に登録します。
※ 配列には、Buf(0)は、「社員番号」:Buf(1)は、「氏名」:Buf(2)は、「メールアドレス」:Buf(3)は、「出身地」が配列に登録されます。
④ 登録された配列を順番にセルへ転記します。
⑤ A列の最終行まで繰り返します。③へ戻る

 

 

 

 

●実行前~実行後 ※A列の文字列データを元に、カンマ区切りで文字列を分割して各項目ごと「社員番号」・「氏名」・「メールアドレス」・「出身地」へ転記されました。
(画面クリックして拡大)

 

 

EXCEL VBA カンマ区切りの文字列を分割して別シートに転記します。(Split関数)

 

 

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

下記のサンプルプログラムは、カンマ区切り文字列を分割して別シートに転記するサンプルプログラムです。

【プログラムの流れ】
① ワークシート「データ」A列の最終行を取得します。
② ワークシート「取扱商品」の文字列をクリアーします。
③ 1行目からワークシート「データ」A列の最終行まで繰り返します。
④ Split関数を使ってカンマ区切りの文字列を分割して配列に登録します。
⑤ 文字列を分割した配列要素分繰り返します。
⑥ 配列に登録したデータをワークシート「取扱商品」へ転記します。
⑦ A列の最終行分繰り返します。④へ

【プログラム実行条件】
①ワークシート「データ」A列にデータを登録します。データの区切り文字は、カンマ”,”で登録する。
②ワークシート「取扱商品」に表を作成します。

 

 

 

 

 ●実行前~実行後 ※プログラム実行後、ワークシート「データ」A列のデータを元に、Split関数を使ってカンマ区切りでワークシート「取扱商品」へ転記されました。
(画面クリックして拡大)

 

 

 

 

EXCEL VBA 複数の区切り記号(文字)混在の文字列を分割して別シートに転記します。(Split関数)

 

 

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

下記のサンプルプログラムは、複数の区切り記号(文字)混在の文字列を分割して別シートに転記するサンプルプログラムです。今回のサンプルプログラムでは、カンマ区切りと半角スペース区切りですが、Replaceメゾットを利用する事で更に複数の文字や記号でも応用できますので、サンプルプログラムを参照にご活用下さい。

【分割条件】
① カンマ ”,”   ② セミコロン”;” ③ 円マーク ”¥”


【処理内容】
●ワークシート「データ」には、3種類の区切り文字を利用しています。この3種類の区切り文字をReplaceメゾットを利用して1種類の区切り文字(カンマ”,”)に変換して分割を行います。

【プログラムの流れ】

① ワークシート「データ」A列の最終行を取得します。
② ワークシート「社員情報」の文字列をクリアーします。
③ 1行目からA列の最終行まで繰り返します。
④ Replaceメゾットを利用して文字列内の”;” ⇒ ”,”  ”¥” ⇒ ”,” へ文字列変換します。
⑤ Split関数を使ってカンマ区切り文字列を分割します。
⑥ 分割した配列要素分を繰り返します。
⑦ 配列に分割した文字列を別シートに列ごと転記します。
⑧ ワークシート「データ」の最終行まで繰り返します。④へ

 

【プログラム実行条件】

① ワークシート「データ」のA列にデータを登録します。
② ワークシート「社員情報」を作成します。

●【Replaceメゾットについては、下記を参照して下さい】

EXCEL VBA Replace メソッド 複数の文字列の置換(セル範囲・一括変換・置換リスト)

 

 

 

 ●実行前~実行後  ※プログラム実行後、ワークシート「データ」のA列に登録されている内容に応じて、複数の区切り文字で分割されているデータを別シートに転記され区切り文字ごと各項目へ転記されました。
(画面クリックして拡大)

 

 

 

 

 

EXCEL VBA フォルダーの階層ごとに分割してフォルダー名・ファイル名を別々のセルに格納する(Split関数)

 

 

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

下記のサンプルプログラムは、指定したフォルダー全ての階層をEXCELシートの各セルに出力します。出力する際は、階層ごとにフォルダー及びファイルを分けてセルに登録します。今回のプログラムは、過去に紹介した再帰呼び出しを利用し、選択した全てのフォルダー・ファイルを網羅します。詳細については、下記リンクを参照して下さい。

【プログラムの流れ】

●filseList01(メインプログラム)
① ワークシートをクリアーします。
② ダイアログボックスが表示されてフォルダーを選択します。
③ フォルダーが選択されていれば、サブプログラムを実行します。フォルダーが選択されていない場合は、プログラム終了。

●fileList_sub(サブプログラム)
④ 選択フォルダー内の最初のファイルを選択します。
⑤ フォルダー内の全てのファイルを繰り返します。
⑥ セルA1から行番号を取得します。取得した行番号を加算します。+1
⑦ Split関数を使って円マーク区切りにファイルパスを分割して各セルに転記します。
⑧ セルA1に件数を増やす。※このセルA1に件数をカウントさせて、次の転記する行を指定する事が出来ます。
⑨ 次のファイルが無くなるまでフォルダー内のファイルを選択します。
⑩ 再帰呼び出しを使って全てのフォルダーを繰り返します。④へ

●filseList01(メインプログラム)
⑪ 選択範囲の以下のフォルダーファイルを分割転記作業がを終わったら、セルA1からなる表範囲(データ範囲)を取得します。
⑫ 表範囲の格子罫線を表範囲に引きます。
⑬ 表の最終列(最大値)を把握して1行目の表題に階層番号を表示させます。
⑭ 表題に、背景色を指定します。

【プログラム実行条件】
① 下記の2つのプログラムは、【fileList01】がメインです。【fileList_sub】は、サブプログラムです。プログラムの実行は、メインを実行します。

EXCEL VBA フォルダー内のファイル名をシートに一覧表示(サブフォルダー含める:再帰呼び出し)テクニック

 

 

 

 

 

 ●実行前~実行後 ※プログラム実行後、ダイアログボックスが表示されるので、リスト化したい(フルパス)フォルダーを選択します。選択したフォルダーの階層ごとに分割されてEXCELシートに転記されました。
(画面クリックして拡大)

 

 

 

 

 

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