ここでは Offset × 条件分岐 × Split を組み合わせて「セルの文字列を分割し、隣列や複数列に展開する」黄金パターンをまとめます。
売上表・勤怠表・在庫表などでよくある 複合データの分解・正規化 を自動化できます。
基本パターン 10選
1. 氏名を姓と名に分割
Dim r As Range, parts As Variant
For Each r In Range("A2:A20")
If InStr(r.Value, " ") > 0 Then
parts = Split(r.Value, " ")
r.Offset(0, 1).Value = parts(0) ' 姓
r.Offset(0, 2).Value = parts(1) ' 名
End If
Next r
👉 「山田 太郎」を「山田」「太郎」に分割。
2. 商品コードを「カテゴリ」と「番号」に分割
Dim r As Range, parts As Variant
For Each r In Range("B2:B20")
If InStr(r.Value, "-") > 0 Then
parts = Split(r.Value, "-")
r.Offset(0, 1).Value = parts(0) ' カテゴリ
r.Offset(0, 2).Value = parts(1) ' 番号
End If
Next r
👉 「FRU-001」を「FRU」「001」に分割。
3. 日付文字列を「年・月・日」に分割
Dim r As Range, parts As Variant
For Each r In Range("C2:C20")
If InStr(r.Value, "/") > 0 Then
parts = Split(r.Value, "/")
r.Offset(0, 1).Value = parts(0) ' 年
r.Offset(0, 2).Value = parts(1) ' 月
r.Offset(0, 3).Value = parts(2) ' 日
End If
Next r
👉 「2025/11/28」を「2025」「11」「28」に分割。
4. 勤怠時刻を「開始」と「終了」に分割
Dim r As Range, parts As Variant
For Each r In Range("D2:D20")
If InStr(r.Value, "-") > 0 Then
parts = Split(r.Value, "-")
r.Offset(0, 1).Value = parts(0) ' 開始
r.Offset(0, 2).Value = parts(1) ' 終了
End If
Next r
👉 「09:00-18:00」を「09:00」「18:00」に分割。
5. 在庫情報を「商品名」と「数量」に分割
Dim r As Range, parts As Variant
For Each r In Range("E2:E20")
If InStr(r.Value, ":") > 0 Then
parts = Split(r.Value, ":")
r.Offset(0, 1).Value = parts(0) ' 商品名
r.Offset(0, 2).Value = parts(1) ' 数量
End If
Next r
👉 「りんご:50」を「りんご」「50」に分割。
6. メールアドレスを「ユーザー名」と「ドメイン」に分割
Dim r As Range, parts As Variant
For Each r In Range("F2:F20")
If InStr(r.Value, "@") > 0 Then
parts = Split(r.Value, "@")
r.Offset(0, 1).Value = parts(0) ' ユーザー名
r.Offset(0, 2).Value = parts(1) ' ドメイン
End If
Next r
👉 「taro@example.com」を「taro」「example.com」に分割。
7. 住所を「都道府県」と「市区町村」に分割
Dim r As Range, parts As Variant
For Each r In Range("G2:G20")
If InStr(r.Value, " ") > 0 Then
parts = Split(r.Value, " ")
r.Offset(0, 1).Value = parts(0) ' 都道府県
r.Offset(0, 2).Value = parts(1) ' 市区町村
End If
Next r
👉 「東京都 江東区」を「東京都」「江東区」に分割。
8. 電話番号を「市外局番」と「番号」に分割
Dim r As Range, parts As Variant
For Each r In Range("H2:H20")
If InStr(r.Value, "-") > 0 Then
parts = Split(r.Value, "-")
r.Offset(0, 1).Value = parts(0) ' 市外局番
r.Offset(0, 2).Value = parts(1) ' 番号
End If
Next r
👉 「03-1234」を「03」「1234」に分割。
9. タグを複数列に展開
Dim r As Range, parts As Variant, i As Integer
For Each r In Range("I2:I20")
If InStr(r.Value, ",") > 0 Then
parts = Split(r.Value, ",")
For i = LBound(parts) To UBound(parts)
r.Offset(0, i + 1).Value = parts(i)
Next i
End If
Next r
👉 「A,B,C」を「A」「B」「C」に分割して横展開。
10. フルパスを「フォルダ」と「ファイル名」に分割
Dim r As Range, parts As Variant
For Each r In Range("J2:J20")
If InStr(r.Value, "\") > 0 Then
parts = Split(r.Value, "\")
r.Offset(0, 1).Value = parts(UBound(parts) - 1) ' フォルダ
r.Offset(0, 2).Value = parts(UBound(parts)) ' ファイル名
End If
Next r
👉 「C:\Data\Report.xlsx」を「Data」「Report.xlsx」に分割。
✅ まとめ
- Offset → 分割結果を隣列へ展開
- 条件分岐 → 区切り文字が存在する場合のみ処理
- Split → 文字列を分割して配列化し、複数列に展開
- 黄金パターン → 氏名・商品コード・日付・時刻・在庫・メール・住所・電話・タグ・パスなど
💡 この「データ分割 × Offset × 条件分岐 × Split」パターン集を使えば、売上表・勤怠表・在庫表などの 複合データの正規化・分解処理 を一瞬で自動化できます。
