clean-data-xls

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Chinese

Clean messy data in the active sheet or a specified range.

清理活动工作表或指定区域内的杂乱数据。

If running inside Excel (Office Add-in / Office JS): Use Office JS directly (
```
Excel.run(async (context) => {...})
```
). Read via
```
range.values
```
, write helper-column formulas via
```
range.formulas = [["=TRIM(A2)"]]
```
. The in-place vs helper-column decision still applies.
If operating on a standalone .xlsx file: Use Python/openpyxl.

在Excel内运行（Office 插件 / Office JS）： 直接使用Office JS（
```
Excel.run(async (context) => {...})
```
）。通过
```
range.values
```
读取数据，通过
```
range.formulas = [["=TRIM(A2)"]]
```
写入辅助列公式。仍需决定是直接修改原数据还是使用辅助列。
处理独立.xlsx文件： 使用Python/openpyxl。

If a range is given (e.g.
```
A1:F200
```
), use it
Otherwise use the full used range of the active sheet
Profile each column: detect its dominant type (text / number / date) and identify outliers

Issue	What to look for
Whitespace	leading/trailing spaces, double spaces
Casing	inconsistent casing in categorical columns ( `usa` / `USA` / `Usa` )
Number-as-text	numeric values stored as text; stray `$` , `,` , `%` in number cells
Dates	mixed formats in the same column ( `3/8/26` , `2026-03-08` , `March 8 2026` )
Duplicates	exact-duplicate rows and near-duplicates (case/whitespace differences)
Blanks	empty cells in otherwise-populated columns
Mixed types	a column that's 98% numbers but has 3 text entries
Encoding	mojibake ( `Ã©` , `â€™` ), non-printing characters
Errors	`#REF!` , `#N/A` , `#VALUE!` , `#DIV/0!`

Show a summary table before changing anything:

Column	Issue	Count	Proposed Fix

在进行任何修改前，展示汇总表格：

列	问题	数量	建议修复方案

Prefer formulas over hardcoded cleaned values — where the cleaned output can be expressed as a formula (e.g.
```
=TRIM(A2)
```
,
```
=VALUE(SUBSTITUTE(B2,"$",""))
```
,
```
=UPPER(C2)
```
,
```
=DATEVALUE(D2)
```
), write the formula in an adjacent helper column rather than computing the result in Python and overwriting the original. This keeps the transformation transparent and auditable.
Only overwrite in place with computed values when the user explicitly asks for it, or when no sensible formula equivalent exists (e.g. encoding/mojibake repair)
For destructive operations (removing duplicates, filling blanks, overwriting originals), confirm with the user first
After each category of fix (whitespace → casing → number conversion → dates → dedup), show the user a sample of what changed and get confirmation before moving to the next category
Report a before/after summary of what changed