ここでは Offset × 条件分岐 × Replace × Split × Format を組み合わせて「複合データを検証 → 不要文字を置換 → 分割 → フォーマット統一 → 正規化」まで一気通貫で処理する黄金パターンをまとめます。
売上表・勤怠表・在庫表などでよくある 表記揺れ・複合値・欠損フォーマット を正規化できます。
基本パターン 10選
1. 氏名を「姓」「名」に分割＋Trim整形
Dim r As Range, parts As Variant
For Each r In Range("A2:A20")
If 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) ' 名
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(Replace(r.Value, " ", ""), "-")
r.Offset(0, 1).Value = UCase(parts(0)) ' カテゴリ
r.Offset(0, 2).Value = Format(parts(1), "0000") ' 番号
End If
Next r
👉 商品コードを分割し、カテゴリは大文字化、番号はゼロ埋め。
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 = Format(parts(0), "0000") ' 年
r.Offset(0, 2).Value = Format(parts(1), "00") ' 月
r.Offset(0, 3).Value = Format(parts(2), "00") ' 日
End If
Next r
👉 日付を分割し、ゼロ埋めで統一。
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 = Format(parts(0), "hh:nn")
r.Offset(0, 2).Value = Format(parts(1), "hh:nn")
End If
Next r
👉 勤怠時刻を分割し、時刻フォーマットに統一。
5. 在庫情報を「商品名」「数量」に分割＋Trim整形
Dim r As Range, parts As Variant
For Each r In Range("E2:E20")
If InStr(r.Value, ":") > 0 Then
parts = Split(Trim(r.Value), ":")
r.Offset(0, 1).Value = parts(0)
r.Offset(0, 2).Value = Format(parts(1), "0")
End If
Next r
👉 在庫情報を分割し、数量を数値整形。
6. メールアドレスを「ユーザー名」「ドメイン」に分割＋小文字化
Dim r As Range, parts As Variant
For Each r In Range("F2:F20")
If InStr(r.Value, "@") > 0 Then
parts = Split(LCase(r.Value), "@")
r.Offset(0, 1).Value = parts(0)
r.Offset(0, 2).Value = parts(1)
End If
Next r
👉 メールアドレスを分割し、小文字に統一。
7. 住所を「都道府県」「市区町村」に分割＋Trim整形
Dim r As Range, parts As Variant
For Each r In Range("G2:G20")
If InStr(r.Value, " ") > 0 Then
parts = Split(Trim(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(Replace(r.Value, " ", ""), "-")
r.Offset(0, 1).Value = parts(0)
r.Offset(0, 2).Value = parts(1)
End If
Next r
👉 電話番号を分割し、余分な空白を削除。
9. タグを複数列に展開＋Trim整形
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 = Trim(parts(i))
Next i
End If
Next r
👉 タグを分割し、Trimで整形して横展開。
10. フルパスを「フォルダ」「ファイル名」に分割＋ProperCase整形
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 = StrConv(parts(UBound(parts) - 1), vbProperCase)
r.Offset(0, 2).Value = StrConv(parts(UBound(parts)), vbProperCase)
End If
Next r
👉 フルパスを分割し、フォルダ名・ファイル名を先頭大文字に整形。
✅ まとめ
- Offset → 分割・整形結果を隣列へ展開
- 条件分岐 → 区切り文字や空欄を判定して安全に処理
- Replace → 不要文字や空白を削除
- Split → 複合データを分割して正規化
- Format → 数値・日付・文字列を統一フォーマット化
- 黄金パターン → 氏名・商品コード・日付・時刻・在庫・メール・住所・電話・タグ・パスなど
💡 この「データ正規化 × Offset × 条件分岐 × Replace × Split × Format」パターン集を使えば、売上表・勤怠表・在庫表などの 複合データの正規化・整形処理 を一瞬で自動化できます。
