ここでは Offset × 条件分岐 × Replace × Format を組み合わせて「セルの値を検証し、不要文字を置換しつつフォーマットを統一する」黄金パターンをまとめます。
データクレンジングと整形を同時に行えるため、売上表・勤怠表・在庫表などの品質管理に直結します。
基本パターン 10選
1. 全角スペースを半角に変換し、Trimで整形
Dim r As Range
For Each r In Range("A2:A20")
If InStr(r.Value, " ") > 0 Then
r.Offset(0, 1).Value = Trim(Replace(r.Value, " ", " "))
End If
Next r
👉 全角スペースを半角に置換し、前後の空白を削除。
2. 会社名の「(株)」を削除し、ProperCaseに変換
Dim r As Range
For Each r In Range("B2:B20")
If InStr(r.Value, "(株)") > 0 Then
r.Offset(0, 1).Value = StrConv(Replace(r.Value, "(株)", ""), vbProperCase)
End If
Next r
👉 「(株)」を削除し、先頭大文字に整形。
3. NGワードを「※」に置換し、大文字化
Dim r As Range
For Each r In Range("C2:C20")
If InStr(r.Value, "NG") > 0 Then
r.Offset(0, 1).Value = UCase(Replace(r.Value, "NG", "※"))
End If
Next r
👉 NGワードを「※」に置換し、大文字に統一。
4. ハイフンを削除し、ゼロ埋め4桁に整形
Dim r As Range
For Each r In Range("D2:D20")
If InStr(r.Value, "-") > 0 Then
r.Offset(0, 1).Value = Format(Replace(r.Value, "-", ""), "0000")
End If
Next r
👉 ハイフンを削除し、ゼロ埋めコードに整形。
5. 半角カナを全角カナに変換し、Trimで整形
Dim r As Range
For Each r In Range("E2:E20")
If r.Value <> "" Then
r.Offset(0, 1).Value = Trim(StrConv(r.Value, vbWide))
End If
Next r
👉 半角カナを全角カナに変換し、空白を除去。
6. 数値をカンマ区切りに整形
Dim r As Range
For Each r In Range("F2:F20")
If IsNumeric(r.Value) Then
r.Offset(0, 1).Value = Format(r.Value, "#,##0")
End If
Next r
👉 売上金額をカンマ区切りで整形。
7. 数値を小数点2桁に整形
Dim r As Range
For Each r In Range("G2:G20")
If IsNumeric(r.Value) Then
r.Offset(0, 1).Value = Format(r.Value, "0.00")
End If
Next r
👉 単価などを小数点2桁に統一。
8. 日付を「YYYY/MM/DD」に整形
Dim r As Range
For Each r In Range("H2:H20")
If IsDate(r.Value) Then
r.Offset(0, 1).Value = Format(r.Value, "yyyy/mm/dd")
End If
Next r
👉 日付を統一フォーマットに変換。
9. 時刻を「HH:MM」に整形
Dim r As Range
For Each r In Range("I2:I20")
If IsDate(r.Value) Then
r.Offset(0, 1).Value = Format(r.Value, "hh:nn")
End If
Next r
👉 勤怠表の時刻を「09:00」形式に統一。
10. 特定文字列を置換し、ProperCaseに整形
Dim r As Range
For Each r In Range("J2:J20")
If InStr(r.Value, "東京") > 0 Then
r.Offset(0, 1).Value = StrConv(Replace(r.Value, "東京", "TOKYO"), vbProperCase)
End If
Next r
👉 「東京」を「TOKYO」に置換し、先頭大文字に整形。
✅ まとめ
- Offset → 整形結果を隣列へ出力
- 条件分岐 → 数値判定・日付判定・文字列判定で安全に処理
- Replace → 不要文字や表記揺れを置換
- Format → 数値・日付・文字列を統一フォーマット化
- 黄金パターン → スペース修正・会社名整形・NGワード置換・コード整形・日付/時刻統一など
💡 この「データ整形 × Offset × 条件分岐 × Replace × Format」パターン集を使えば、売上表・勤怠表・在庫表などの データクレンジング＋フォーマット統一 を一瞬で自動化できます。
