Smart Data Cleaning

Standard tier

Available on Standard tier and above.

Wizard that fixes the structural issues commonly found in Excel files received from partners or exported from other software: blank rows, duplicate data, extra whitespace, inconsistent formatting, invisible characters (zero-width space, BOM, non-breaking space).

Open the dialog

DVD Addin → group Text and Number → dropdown Text ops → click Smart Data Cleaning.

3-step workflow

Step 1 — Pick scope

Option	Description
Selection	Only the current selection
Active sheet	Entire current sheet
Multiple sheets	Group sheets first (Ctrl+click tabs)

Step 2 — Tick cleanup options

Group 1 — Whitespace

Option	Action
Trim leading/trailing space	Strips leading + trailing spaces in each cell
Collapse internal whitespace	"Hà Nội" → "Hà Nội"
Remove non-breaking space (NBSP)	Strips U+00A0 (common when copying from the web)
Remove zero-width space	Strips U+200B, U+200C, U+200D, U+FEFF (BOM) — invisible but breaks VLOOKUP
Strip CR/LF inside cell	Removes newlines inside cells

Group 2 — Rows / Cells

Option	Action
Delete blank rows	Deletes rows that have no data in any column
Delete hidden rows	Deletes rows hidden (e.g. after filtering)
Delete duplicate rows	Keeps the first row, deletes rows with matching values in selected columns
Delete cell styles	Cleans up unused styles (workbook with many styles → bloated + slow)

Group 3 — Format

Option	Action
Standardize date format	Detects mixed formats (DD/MM, MM/DD, ISO...) → unifies to DD/MM/YYYY
Strip number formatting	Removes $/đ/separators → lets Excel re-format
Auto-detect number-as-text	Cell '1234' → 1234 (number)
Remove leading apostrophe	Strips leading ' from text cells

Group 4 — Text

Option	Action
Lower / Upper / Title case	Applies case rule
Remove accents	"Hà Nội" → "Ha Noi"
Strip special characters	Drops chars outside alphanumeric + space

Step 3 — Preview + Apply

Click 🔍 Preview:

• Dialog shows on the grid: rows to delete (red highlight), cells to change (yellow highlight), unchanged (no color)
• Summary: "Will delete 12 rows, modify 487 cells in 1,250 affected cells"

Click ✓ Apply changes → executes. Everything goes into Excel's undo stack — Ctrl+Z reverts.

Common use cases

After importing from estimation software

Issue	Tick options
Task-name column has leading spaces + double spaces	Trim + Collapse whitespace
Norm-code column is text with leading apostrophe	Remove leading apostrophe
Subtotal / separator blank rows interspersed	Delete blank rows
Amount column has "đ" + thousand separator	Strip number formatting

After receiving foreign-partner file

Issue	Tick options
Date format MM/DD/YYYY (US)	Standardize date → DD/MM/YYYY
Numbers using comma decimal (EU)	Strip number formatting + manual re-detect
UTF-8 BOM hidden at cell start	Remove zero-width space

Cleanup workbook before save

Issue	Tick options
50MB file with only 1000 rows → bloated	Delete cell styles + Delete blank rows
Filtered before save → hidden rows	Delete hidden rows
Copy from Word with non-printables	Remove NBSP + zero-width

Save profile

Tick options → click 💾 Save profile → name it (e.g. "Import GXD cleanup"). Next time loading the profile = applies the same set of options.

Profile is stored at %APPDATA%\DVDAddin\CleaningProfiles\<name>.json.

License gating

Tier	Available
Trial / Activated	✅ All
Standard	✅ All
Pro	✅ All
Expired / Unactivated	🔒 Locked

Tips

• Backup before Apply: enable "AutoBackup before cleaning" in Preferences → addin saves a copy of the workbook first
• Combine workflow: Data Cleaning first → Bulk Find/Replace after (cleaning whitespace first makes regex matches easier)
• Detect duplicates by subset of columns: dialog lets you pick which columns participate in comparison (e.g. same "Norm code" + "Unit" = duplicate)

Related

• Bulk Find/Replace — pattern-based replacement
• Text utilities — single-cell text operations
• Encoding — TCVN3/Unicode/VNI conversion