EXCEL VBA 文字列を分割・指定文字で区切る・カンマ区切り・配列格納 （Split関数）
EXCEL VBA 文字列を分割・指定文字で区切る・カンマ区切り・配列格納 （Split関数）
今回説明するのは、Split関数の利用方法を説明いたします。Split関数は、１つに纏まった文字列を文字列内の区切り文字で文字列を分割する事ができます。特定の文字を指定して分割する事もできるので、カンマ区切りの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関数のサンプル
【プログラム】
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
' ' Sub Split00() 'Split関数サンプル Dim I As Long Dim Buf As Variant '配列を指定します。 Buf = Split("東京, 神奈川, 千葉", ",") 'Split関数を使って文字列（"東京, 神奈川, 千葉"）を”，”カンマ区切りで分割します。 For I = 0 To UBound(Buf) '配列に分割して格納したデータを繰り返します。 MsgBox Buf(I) '分割したデータを順番にメッセージボックスへ表示します。 Next I End Sub ' |
【注意点】
Split関数が返す配列を要素数を指定した配列変数に格納する事ができません。Split関数が返す配列は、要素数を指定しない動的配列に格納する事が出来ます。
（例）
Dim Buf（２） as Variant ・・・・・ Split関数では配列へ格納できない。（要素数を指定は、×）
Dim Buf（） as Variant ・・・・・・ 配列へ格納できる。（動的配列で〇）
EXCEL VBA 一つの文字列を分割して別々の列に転記します。（Split関数）
下記のサンプルプログラムは、一つの文字列データに対して文字列を分割して別々の列（セル）に転記するサンプルプログラムです。A列にはカンマ区切りの文字列データが登録されています。（文字列データ：社員番号、氏名、メールアドレス、出身地）この文字列データをC列～F列の各項目へ転記するサンプルプログラムです。
【プログラムの流れ】
① A列の最終行を取得します。
② 2行目からA列の最終行まで繰り返します。
③ Split関数を使ってカンマ区切りの文字列を分割して配列に登録します。
※ 配列には、Buf（０）は、「社員番号」：Buf（１）は、「氏名」：Buf（２）は、「メールアドレス」：Buf（３）は、「出身地」が配列に登録されます。
④ 登録された配列を順番にセルへ転記します。
⑤ A列の最終行まで繰り返します。③へ戻る
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
' ' Sub Split01() 'A列に登録されている文字列を分割して別々の列に転記します。 Dim i, L, lRow As Long Dim Buf As Variant lRow = Cells(Rows.Count, "A").End(xlUp).Row 'A列の最終行を取得します。 For i = 2 To lRow '2行目からA列の最終行まで繰り返します。 Buf = Split(Cells(i, 1), ",") 'Split関数を使ってカンマ区切りの文字列を分割して配列に登録します。 For L = 0 To UBound(Buf) '文字列を分割した配列要素分繰り返します。 Cells(i, L + 3) = Buf(L) '配列に分割した文字列をセルに転記します。 Next L Next i End Sub ' |
（画面クリックして拡大）
EXCEL VBA カンマ区切りの文字列を分割して別シートに転記します。（Split関数）
下記のサンプルプログラムは、カンマ区切り文字列を分割して別シートに転記するサンプルプログラムです。
【プログラムの流れ】
① ワークシート「データ」A列の最終行を取得します。
② ワークシート「取扱商品」の文字列をクリアーします。
③ １行目からワークシート「データ」A列の最終行まで繰り返します。
④ Split関数を使ってカンマ区切りの文字列を分割して配列に登録します。
⑤ 文字列を分割した配列要素分繰り返します。
⑥ 配列に登録したデータをワークシート「取扱商品」へ転記します。
⑦ A列の最終行分繰り返します。④へ
【プログラム実行条件】
①ワークシート「データ」A列にデータを登録します。データの区切り文字は、カンマ”，”で登録する。
②ワークシート「取扱商品」に表を作成します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
' ' Sub Split02() '別シートに項目ごとに転記します。 Dim ws01, ws02 As Worksheet Dim i, L, lRow As Long Dim Buf As Variant Set ws01 = Worksheets("データ") Set ws02 = Worksheets("取扱商品") lRow = ws01.Cells(Rows.Count, "A").End(xlUp).Row 'ワークシート「データ」A列の最終行を取得します。 ws02.Cells.ClearContents 'ワークシート「取扱商品」を文字列クリアーします。 For i = 1 To lRow '１行目からA列の最終行まで繰り返します。 Buf = Split(ws01.Cells(i, 1), ",") 'Split関数を使ってカンマ区切りの文字列を分割して配列に登録します。 For L = 0 To UBound(Buf) '文字列を分割した配列要素分繰り返します。 ws02.Cells(i, L + 1) = Buf(L) '配列に分割した文字列をセルに転記します。 Next L Next i End Sub ' |
（画面クリックして拡大）
EXCEL VBA 複数の区切り記号（文字）混在の文字列を分割して別シートに転記します。（Split関数）
下記のサンプルプログラムは、複数の区切り記号（文字）混在の文字列を分割して別シートに転記するサンプルプログラムです。今回のサンプルプログラムでは、カンマ区切りと半角スペース区切りですが、Replaceメゾットを利用する事で更に複数の文字や記号でも応用できますので、サンプルプログラムを参照にご活用下さい。
【分割条件】
① カンマ ”,” ② セミコロン”；” ③ 円マーク ”￥”
【処理内容】
●ワークシート「データ」には、３種類の区切り文字を利用しています。この３種類の区切り文字をReplaceメゾットを利用して1種類の区切り文字（カンマ”,”）に変換して分割を行います。
【プログラムの流れ】
① ワークシート「データ」A列の最終行を取得します。
② ワークシート「社員情報」の文字列をクリアーします。
③ 1行目からA列の最終行まで繰り返します。
④ Replaceメゾットを利用して文字列内の”；” ⇒ ”,” ”￥” ⇒ ”,” へ文字列変換します。
⑤ Split関数を使ってカンマ区切り文字列を分割します。
⑥ 分割した配列要素分を繰り返します。
⑦ 配列に分割した文字列を別シートに列ごと転記します。
⑧ ワークシート「データ」の最終行まで繰り返します。④へ
【プログラム実行条件】
① ワークシート「データ」のA列にデータを登録します。
② ワークシート「社員情報」を作成します。
●【Replaceメゾットについては、下記を参照して下さい】
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
' ' Sub Split03() '複数の区切り記号（文字）混在の文字列を分割して別シートに転記します。 Dim ws01, ws02 As Worksheet Dim i, L, lRow As Long Dim Buf As Variant Set ws01 = Worksheets("データ") Set ws02 = Worksheets("社員情報") lRow = ws01.Cells(Rows.Count, "A").End(xlUp).Row 'ワークシート「データ」A列の最終行を取得します。 ws02.Cells.ClearContents 'ワークシート「社員情報」の文字列をクリアーします。 For i = 1 To lRow '１行目からA列の最終行まで繰り返します。 Buf = Replace(ws01.Cells(i, 1), ";", ",") 'Replaceメゾットを利用して”；”⇒”,”へ変換 Buf = Replace(Buf, "\", ",") 'Replaceメゾットを利用して”￥”⇒ ”,”へ変換 Buf = Split(Buf, ",") 'Split関数を使ってカンマ区切りの文字列を分割して配列に登録します。 For L = 0 To UBound(Buf) '文字列を分割した配列要素分繰り返します。 ws02.Cells(i, L + 1) = Buf(L) '配列に分割した文字列をセルに転記します。 Next L Next i End Sub ' |
（画面クリックして拡大）
EXCEL VBA フォルダーの階層ごとに分割してフォルダー名・ファイル名を別々のセルに格納する（Split関数）
下記のサンプルプログラムは、指定したフォルダー全ての階層をEXCELシートの各セルに出力します。出力する際は、階層ごとにフォルダー及びファイルを分けてセルに登録します。今回のプログラムは、過去に紹介した再帰呼び出しを利用し、選択した全てのフォルダー・ファイルを網羅します。詳細については、下記リンクを参照して下さい。
【プログラムの流れ】
●filseList01(メインプログラム)
① ワークシートをクリアーします。
② ダイアログボックスが表示されてフォルダーを選択します。
③ フォルダーが選択されていれば、サブプログラムを実行します。フォルダーが選択されていない場合は、プログラム終了。
●fileList_sub(サブプログラム）
④ 選択フォルダー内の最初のファイルを選択します。
⑤ フォルダー内の全てのファイルを繰り返します。
⑥ セルA1から行番号を取得します。取得した行番号を加算します。＋１
⑦ Split関数を使って円マーク区切りにファイルパスを分割して各セルに転記します。
⑧ セルA1に件数を増やす。※このセルA1に件数をカウントさせて、次の転記する行を指定する事が出来ます。
⑨ 次のファイルが無くなるまでフォルダー内のファイルを選択します。
⑩ 再帰呼び出しを使って全てのフォルダーを繰り返します。④へ
●filseList01(メインプログラム)
⑪ 選択範囲の以下のフォルダーファイルを分割転記作業がを終わったら、セルA1からなる表範囲（データ範囲）を取得します。
⑫ 表範囲の格子罫線を表範囲に引きます。
⑬ 表の最終列（最大値）を把握して１行目の表題に階層番号を表示させます。
⑭ 表題に、背景色を指定します。
【プログラム実行条件】
① 下記の２つのプログラムは、【fileList01】がメインです。【fileList_sub】は、サブプログラムです。プログラムの実行は、メインを実行します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 |
' ' '******** AKIRA55.COM ******* https://akira55.com/ ' Sub fileList01() '選択したフォルダー（サブフォルダー含む）のファイル一覧作成 Dim Add As Range Dim Hani As String Dim I, lCol As Long Cells.Clear 'シート内容をクリアー Range("A1") = 1 With Application.FileDialog(msoFileDialogFolderPicker) 'ダイアログボックスが表示されてフォルダー選択します。 If .Show = True Then Call fileList_sub(.SelectedItems(1)) 'フォルダーが選択されていれば、サブプログラムを実行 Else MsgBox "フォルダーを選択しませんでした。" Exit Sub 'プログラム終了 End If End With Set Add = Range("A1").CurrentRegion 'セルA1からなる表範囲を取得する。 Add.Borders.LineStyle = xlContinuous '格子罫線を作成：細実線 Range("B1") = "ドライブ" lCol = Add.Columns(Add.Columns.Count).Column ' 表の最終列を取得します。 For I = 3 To lCol '先頭行のC列から最終列まで階層番号を記載する。 Cells(1, I) = "階層" & I - 2 Next I For Each Add In Range(Cells(1, 1), Cells(1, lCol)) 'A1～ Add.Interior.ColorIndex = 20 '背景色を指定します。 Next Add End Sub ' ' ' Sub fileList_sub(Path As String) '選択フォルダーパスを受け取りフォルダー内のファイル一覧を作成します。 Dim Sub_forlder As Object Dim Temp As String Dim I, F, L As Long Dim buf As Variant Temp = Dir(Path & "\*.*") '選択ファルダー内の最初のファイルを選択します。 Do While Temp <> "" '全てのファイルを繰り返します。 I = Range("A1") '行番号を代入 I = I + 1 'ファイル内容を転記する行番号を加算します。 Cells(I, "A") = I - 1 'A列に件数を表示します。 buf = Path & "\" & Temp 'フォルダーパスとファイル名を表示します。 buf = Split(buf, "\") 'Split関数を使って円マーク区切りの文字列を分割して配列に登録します。 For L = 0 To UBound(buf) '文字列を分割した配列要素分繰り返します。 Cells(I, L + 2) = buf(L) '配列に分割した文字列をセルに転記します。 Next L Range("A1") = I 'セルA1に件数カウントする。 Temp = Dir() '次のファイルを選択します。 Loop With CreateObject("Scripting.FileSystemObject") For Each Sub_forlder In .GetFolder(Path).SubFolders 'すべてのフォルダーを繰り返します。 Call fileList_sub(Sub_forlder.Path) '再帰呼び出しを行いフォルダパスからサブフォルダ内容を取得 Next Sub_forlder End With End Sub ' ' |
（画面クリックして拡大）
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。