ここでは Offset × 条件分岐 × Replace × Split × Format を組み合わせて「セルの値を検証 → 不要文字を置換 → 分割 → フォーマット統一 → 結果を隣列に出力」する黄金パターンをまとめます。
入力チェックと正規化を同時に行えるため、売上表・勤怠表・在庫表などの 複合データクレンジング＋正規化 に直結します。
基本パターン 10選
1. 氏名を検証＋分割＋ProperCase整形
Dim r As Range, parts As Variant
For Each r In Range("A2:A20")
If r.Value <> "" And InStr(r.Value, " ") > 0 Then
parts = Split(Trim(r.Value), " ")
r.Offset(0, 1).Value = StrConv(parts(0), vbProperCase) ' 姓
r.Offset(0, 2).Value = StrConv(parts(1), vbProperCase) ' 名
Else
r.Offset(0, 1).Value = "未入力"
r.Offset(0, 2).Value = "未入力"
End If
Next r
👉 氏名を「姓」「名」に分割し、ProperCaseで整形。
2. 商品コードを検証＋Replace＋分割＋ゼロ埋め
Dim r As Range, parts As Variant
For Each r In Range("B2:B20")
If r.Value <> "" And InStr(r.Value, "-") > 0 Then
parts = Split(Replace(r.Value, " ", ""), "-")
r.Offset(0, 1).Value = UCase(parts(0)) ' カテゴリ
r.Offset(0, 2).Value = Format(parts(1), "0000") ' 番号
Else
r.Offset(0, 1).Value = "N/A"
r.Offset(0, 2).Value = "0000"
End If
Next r
👉 商品コードを「カテゴリ」「番号」に分割し、ゼロ埋め。
3. 日付文字列を検証＋分割＋Format統一
Dim r As Range, parts As Variant
For Each r In Range("C2:C20")
If IsDate(r.Value) Then
parts = Split(Format(r.Value, "yyyy/mm/dd"), "/")
r.Offset(0, 1).Value = parts(0)
r.Offset(0, 2).Value = parts(1)
r.Offset(0, 3).Value = parts(2)
Else
r.Offset(0, 1).Value = "0000"
r.Offset(0, 2).Value = "00"
r.Offset(0, 3).Value = "00"
End If
Next r
👉 日付を「年・月・日」に分割し、ゼロ埋めで統一。
4. 勤怠時刻を検証＋分割＋Format統一
Dim r As Range, parts As Variant
For Each r In Range("D2:D20")
If r.Value <> "" And InStr(r.Value, "-") > 0 Then
parts = Split(r.Value, "-")
r.Offset(0, 1).Value = Format(parts(0), "hh:nn")
r.Offset(0, 2).Value = Format(parts(1), "hh:nn")
Else
r.Offset(0, 1).Value = "00:00"
r.Offset(0, 2).Value = "00:00"
End If
Next r
👉 勤怠時刻を「開始」「終了」に分割し、時刻フォーマットに統一。
5. 在庫情報を検証＋Replace＋分割＋数値整形
Dim r As Range, parts As Variant
For Each r In Range("E2:E20")
If r.Value <> "" And InStr(r.Value, ":") > 0 Then
parts = Split(Replace(r.Value, " ", ""), ":")
r.Offset(0, 1).Value = parts(0)
r.Offset(0, 2).Value = Format(parts(1), "0")
Else
r.Offset(0, 1).Value = "不明"
r.Offset(0, 2).Value = 0
End If
Next r
👉 在庫情報を「商品名」「数量」に分割し、数値整形。
6. メールアドレスを検証＋分割＋小文字化
Dim r As Range, parts As Variant
For Each r In Range("F2:F20")
If r.Value <> "" And InStr(r.Value, "@") > 0 Then
parts = Split(LCase(r.Value), "@")
r.Offset(0, 1).Value = parts(0)
r.Offset(0, 2).Value = parts(1)
Else
r.Offset(0, 1).Value = "unknown"
r.Offset(0, 2).Value = "domain.com"
End If
Next r
👉 メールアドレスを「ユーザー名」「ドメイン」に分割し、小文字化。
7. 住所を検証＋分割＋Trim整形
Dim r As Range, parts As Variant
For Each r In Range("G2:G20")
If r.Value <> "" And InStr(r.Value, " ") > 0 Then
parts = Split(Trim(r.Value), " ")
r.Offset(0, 1).Value = parts(0)
r.Offset(0, 2).Value = parts(1)
Else
r.Offset(0, 1).Value = "不明"
r.Offset(0, 2).Value = "不明"
End If
Next r
👉 住所を「都道府県」「市区町村」に分割し、Trimで整形。
8. 電話番号を検証＋Replace＋分割
Dim r As Range, parts As Variant
For Each r In Range("H2:H20")
If r.Value <> "" And InStr(r.Value, "-") > 0 Then
parts = Split(Replace(r.Value, " ", ""), "-")
r.Offset(0, 1).Value = parts(0)
r.Offset(0, 2).Value = parts(1)
Else
r.Offset(0, 1).Value = "000"
r.Offset(0, 2).Value = "0000"
End If
Next r
👉 電話番号を「市外局番」「番号」に分割し、空白を削除。
9. タグを検証＋Split＋Trim整形
Dim r As Range, parts As Variant, i As Integer
For Each r In Range("I2:I20")
If r.Value <> "" And InStr(r.Value, ",") > 0 Then
parts = Split(r.Value, ",")
For i = LBound(parts) To UBound(parts)
r.Offset(0, i + 1).Value = Trim(parts(i))
Next i
Else
r.Offset(0, 1).Value = "none"
End If
Next r
👉 タグを分割し、Trimで整形して横展開。
10. フルパスを検証＋Split＋ProperCase整形
Dim r As Range, parts As Variant
For Each r In Range("J2:J20")
If r.Value <> "" And InStr(r.Value, "\") > 0 Then
parts = Split(r.Value, "\")
r.Offset(0, 1).Value = StrConv(parts(UBound(parts) - 1), vbProperCase)
r.Offset(0, 2).Value = StrConv(parts(UBound(parts)), vbProperCase)
Else
r.Offset(0, 1).Value = "UnknownFolder"
r.Offset(0, 2).Value = "UnknownFile"
End If
Next r
👉 フルパスを「フォルダ」「ファイル名」に分割し、ProperCase整形。
✅ まとめ
- Offset → 正規化結果を隣列へ展開
- 条件分岐 → 空欄判定・区切り文字判定・型判定で安全に処理
- Replace → 不要文字や空白を削除
- Split → 複合データを分割して正規化
- Format → 数値・日付・文字列を統一フォーマット化
この複合黄金パターンの強み
- 検証＋整形＋正規化 を一括で処理できるため、実務でのデータ品質を一気に改善
- 柔軟性：条件分岐で「空欄なら補完」「入力済みなら整形」「複合データなら分割」と切り替え可能
- 汎用性：売上表・勤怠表・在庫表・顧客リストなど、あらゆる業務データに適用可能
- 再利用性：Replace・Split・Format の組み合わせをテンプレ化すれば、どんなデータでも即対応
💡 この「検証＋正規化 × Offset × 条件分岐 × Replace × Split × Format」パターン集を使えば、 データクレンジング → 欠損補完 → 正規化 → フォーマット統一 を一瞬で自動化できます。
