セルの文字列を指定した文字列で別のセルに分割する方法
セルに入力されている文字列は指定した文字列を区切り文字として別のセルに分割することができます。
文字列の中で指定した文字列が先頭の文字から数えて何番目にあるかを確認するInstr【インストリング】関数とIf文て文字列の中に指定した文字列が存在するかを確認し、Split【スプリット】関数を使用して、その指定した文字列を区切り文字として配列にして格納しその配列の要素を各セルに振り分けます。
A列の名前を半角スペースを区切り文字としてB列に姓、C列に名に分割するコードと解説
Sub 分割() Application.ScreenUpdating = False Dim i As Long Dim maxrow As Long maxrow = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To maxrow If InStr(Cells(i, 1), " ") > 0 Then Cells(i, 2) = Split(Cells(i, 1), " ")(0) Cells(i, 3) = Split(Cells(i, 1), " ")(1) End If Next i Range("B1") = "姓" Range("C1") = "名" Application.ScreenUpdating = True End Sub
Application【アプリケーション】オブジェクトのScreenUpdating 【スクリーンアップディーティング】プロパティにFalseを設定して、メモリ使用の抑制と処理の高速化のために、画面の更新を抑制します。
3行目【 Dim i As Long 】
6行目のFor文で使う、2行目~データの最終行を格納するカウンター変数「i」を長整数型(Long)で宣言します。
4行目【 Dim maxrow As Long 】
A列のデータ最終行番号を格納する変数「maxrow」を長整数型(Long)で宣言します。
5行目【 maxrow = Cells(Rows.Count, 1).End(xlUp).Row 】
Range【レンジ】オブジェクトのEnd【エンド】プロパティを使用して、A列の一番下のセルから上に上がってデータが入力されているセルの行番号を取得し、変数「maxrow」に代入しています。
6行目【For i = 2 To maxrow】
For文で、2から変数「maxrow」までの繰り返し処理の始まりです。
7行目【 If InStr(Cells(i, 1), ” “) > 0 Then 】
If文で、Instr【インストリング】関数を使い
1列目の文字列に半角スペースが何番目にあるか確認し比較演算子で0番目より大きい場合、条件分岐で8行目の処理を行うことを定義しています。つまり文字列の中に半角スペースがあるかないかを判別しています。
8行目【 Cells(i, 2) = Split(Cells(i, 1), ” “)(0) 】
i行目のB列のセルにSplit関数で半角スペースで分割した文字列の配列の最初の要素を代入しています。
9行目
【 Cells(i, 3) = Split(Cells(i, 1), ” “)(1) 】
i行目のC列のセルにSplit関数で半角スペースで分割した文字例の配列の2番目の要素を代入しています。
12行目【 Range(“B1”) = “姓” 】
B1セルに「姓」の文字列を代入しています。
13行目【 Range(“C1”) = “名” 】
C1セルに「名」の文字列を代入しています。
14行目【Application.ScreenUpdating = True】
止めていた画面の更新を再開しています。
実行結果
住所データ列を都道府県と市町村に分割する方法
都道府県と市町村に分割するコードと解説
Sub 分割() Application.ScreenUpdating = False Dim i As Long Dim maxrow As Long maxrow = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To maxrow If InStr(Cells(i, 1), "都") > 0 Then Cells(i, 2) = Split(Cells(i, 1), "都")(0) Cells(i, 2) = Cells(i, 2) & "都" Cells(i, 3) = Split(Cells(i, 1), "都")(1) End If Next i For i = 2 To maxrow If InStr(Cells(i, 1), "道") > 0 Then Cells(i, 2) = Split(Cells(i, 1), "道")(0) Cells(i, 2) = Cells(i, 2) & "道" Cells(i, 3) = Split(Cells(i, 1), "道")(1) End If Next i For i = 2 To maxrow If InStr(Cells(i, 1), "府") > 0 Then Cells(i, 2) = Split(Cells(i, 1), "府")(0) Cells(i, 2) = Cells(i, 2) & "府" Cells(i, 3) = Split(Cells(i, 1), "府")(1) End If Next i For i = 2 To maxrow If InStr(Cells(i, 1), "県") > 0 Then Cells(i, 2) = Split(Cells(i, 1), "県")(0) Cells(i, 2) = Cells(i, 2) & "県" Cells(i, 3) = Split(Cells(i, 1), "県")(1) End If Next i Range("B1") = "都道府県" Range("C1") = "市町村" Application.ScreenUpdating = True End Sub
Sprit関数の検索する文字列は検索が終わると削除されますので
削除された検索文字列を足す必要があります。例えば、
東京都の場合は配列(0)に東京だけが格納されていますので
9行目のCells(i, 2) = Cells(i, 2) & “都”で
都を&(アンパサンド)で足してあげる必要があります。