Smart Data Cleaning…

Location on ribbon: Tab DVD Addin → Group Text and Number → Menu Text ops

Command ID: btnDataCleaning

What it does

The command opens the Smart Data Cleaning window, which bundles six clean-up jobs into one run: trimming whitespace, normalising case, repairing Vietnamese encoding damage (mojibake), normalising date formats, normalising Vietnamese phone numbers and removing duplicate rows.

It suits tables assembled from many sources: site access lists, subcontractor contact sheets, material logs merged from files written by different people. The window skips formula cells and blanks, so it is safe to run over a sheet that already contains calculations.

How to use

1. Select the range to clean in Excel.
2. Go to tab DVD Addin → group Text and Number → menu Text ops → Smart Data Cleaning….
3. In the window, tick the operations you want:
   • Normalize extra whitespace (on by default) — with two sub-options: Merge consecutive spaces into one and Normalize special whitespace (NBSP, zero-width…).
   • Normalize case — pick a Style: UPPERCASE, lowercase, Title Case (default) or Sentence case.
   • Fix Vietnamese font — repairs broken strings such as Cống back into Cộng.
   • Normalize dates — pick a Target format: dd/MM/yyyy, yyyy-MM-dd or MM/dd/yyyy.
   • Normalize phone numbers — rewrites Vietnamese numbers as 0xxxx xxx xxx, including ones written with +84.
   • Remove duplicate rows — choose Exact match or Fuzzy match ≥ with a percentage threshold (85 by default).
4. Click Apply. The status line at the bottom shows progress and then the result, e.g. "✓ Done — fixed 214 cell(s), removed 6 duplicate row(s)".
5. The window does not lock Excel: select another range and click Apply again. Click Close when done.

Example

A site access list arrives from three crews in three different files and is pasted into one sheet: names in mixed casing, rows carrying hard spaces copied from a web page, phone numbers written sometimes with +84 and sometimes with 0, and a few people entered twice.

Select the whole table, open the command, tick Normalize extra whitespace (keeping both sub-options), tick Normalize case with Title Case, tick Normalize phone numbers, tick Remove duplicate rows with Fuzzy match ≥ 90%, then click Apply. The result is a consistent list of names, one phone format and no repeated rows.

Notes

• You must select the range first; otherwise the window reports "Please select a data range in Excel".
• Formula cells and blank cells are skipped, so your calculated values are not overwritten.
• Remove duplicate rows deletes the entire row that contains a matching cell in the selection — including columns outside the part you are looking at. This is the riskiest option in the window.
• Fuzzy match works on a percentage threshold; setting it too low can delete two genuinely different rows. Start around 90–95%.
• Ctrl+Z cannot undo this — save the file (or duplicate the sheet) before clicking Apply.
• With no operation ticked the window reports "No operation selected" and does nothing.

Related

• Remove extra spaces — the one-click version when trimming is all you need.
• Bulk find / replace… — keyword-driven edits with a per-cell preview.
• Auto format — turn the normalised date and number strings into real data types.
• Encoding — use this when the display problem is a TCVN3/VNI font rather than mojibake.