Smart Data Cleaning

When an Excel table is assembled from several sources — three crews sending three files, data pasted out of a PDF, a subcontractor contact list copied from a web page — it always carries the same set of defects: stray whitespace, inconsistent casing, Vietnamese text broken by an encoding mismatch, dates written a different way on every row, phone numbers sometimes with +84 and sometimes with 0, and rows entered twice.

The Text ops menu (tab DVD Addin → group Text and Number) covers that whole set, with Smart Data Cleaning as the window that bundles several jobs into one run.

Commands in this group

Command	What it does
Smart Data Cleaning…	One window with six jobs: trim whitespace, normalise case, repair Vietnamese encoding, normalise dates, normalise phone numbers, remove duplicate rows
Remove extra spaces	The one-click version: runs TRIM over every text cell in the selection
Auto format	Resets the range to General, then converts parseable strings into real numbers and real dates
Remove accents	Strips Vietnamese diacritics (Đường → Duong) for file names, folders and lookup keys
Bulk find / replace…	Find & Replace with a per-cell preview, wildcards/regex, across sheets or workbooks

Where the boundaries lie: Remove extra spaces handles ordinary spaces only, while Smart Data Cleaning also reaches NBSP and zero-width characters; Smart Data Cleaning normalises date strings into one pattern, while Auto format is what converts them into real date values.

Cleaning a table assembled from several sources

1. Save the file or duplicate the sheet first. Nothing in this group can be undone with Ctrl+Z.
2. Select the data range in Excel, then open Text ops ▸ Smart Data Cleaning…. With no selection the window reports "Please select a data range in Excel".
3. Tick Normalize extra whitespace (on by default) together with its two sub-options: Merge consecutive spaces into one and Normalize special whitespace (NBSP, zero-width…).
4. Tick Normalize case and pick a Style: UPPERCASE, lowercase, Title Case (default) or Sentence case.
5. Text showing as Cống instead of Cộng? Tick Fix Vietnamese font. If the damage comes from a TCVN3/VNI code page rather than mojibake, use Encoding instead.
6. Tick Normalize dates and choose a Target format (dd/MM/yyyy, yyyy-MM-dd or MM/dd/yyyy); tick Normalize phone numbers to rewrite Vietnamese numbers as 0xxxx xxx xxx.
7. Tick Remove duplicate rows — choose Exact match or Fuzzy match ≥ with a percentage threshold (85 by default). This is the riskiest option in the window.
8. Click Apply. The status line reports something like "✓ Done — fixed 214 cell(s), removed 6 duplicate row(s)". The window does not lock Excel: select another range and click Apply again, then Close.
9. Number or date columns still left-aligned (SUM returning 0)? Select each column on its own and run Auto format, then reapply the display format (#,##0.00, dd/mm/yyyy) since the command leaves the range on General.

Example

A site access list sent in by three crews and pasted into one sheet:

Before	After Apply
nguyễn văn an — +84 912345678	Nguyễn Văn An — 0912 345 678
TRẦN THỊ BÌNH — 0987.654.321	Trần Thị Bình — 0987 654 321
Lê Văn Cường — 0912345678	Lê Văn Cường — 0912 345 678
nguyễn văn an — 0912345678	(removed — matches row 1 at ≥ 90%)

Ticks used for this run: Normalize extra whitespace (both sub-options) + Normalize case as Title Case + Normalize phone numbers + Remove duplicate rows as Fuzzy match ≥ 90%.

Tips and caveats

• Remove duplicate rows deletes the entire row holding a matching cell — including columns outside the part you are looking at. Setting the Fuzzy match threshold too low can delete two genuinely different rows; start at 90–95%.
• The window skips formula cells and blanks, so it is safe over a sheet that still calculates. By contrast Remove extra spaces and Remove accents overwrite a formula cell with its static value — keep formulas out of those selections.
• With no operation ticked the window reports "No operation selected" and does nothing.
• Auto format recognises values using the machine's Regional Settings: on a vi-VN machine . is the thousands separator, so 1.5 may be read as 15, and 01/02/2026 is read in day/month order. Test a few rows before running a whole column.
• Trim whitespace before doing lookups: stray spaces are the classic reason VLOOKUP and SUMIF stop matching work codes that look identical on screen.
• Remove accents is lossy: Hòa, Hoa and Họa all become Hoa — never use a de-accented column as a unique key.
• In Bulk find / replace, Replace all stays disabled until Preview has returned matches, and any parameter change invalidates the previous preview. The All open workbooks scope also touches other files open on the machine.
• Save a settings combination you reuse (say, standardising material names for submissions) as a Preset: click Save… next to Preset:, then pick the name and click Load next time.
• Choose Values only under Apply to when you only mean to change displayed text — the other two modes edit inside formulas and can break references.

See also

• Bulk find / replace — keyword-driven edits with preview and the Σ Sum helper.
• Text utilities — the remaining commands of the Text ops menu.
• Encoding — TCVN3 / VNI / Unicode when the display problem comes from the code page.
• Cells and Rows — the structural half: blank rows, hidden rows, junk styles, broken Defined Names.