Clean an Excel list: whitespace, character width, then duplicates
A copied Excel column may contain values that look identical but are not the same string. Trim surrounding whitespace first, normalize character width next, and remove duplicates last. Keep the original column and paste the result into a separate column so you can check what changed.
Follow three product codes down to two
These fictional codes include surrounding spaces on the first line and full-width letters and digits on the second. Full-width Latin characters are common in text copied from Japanese systems.
A001 Ａ００１ B002
- Paste the list into Text Cleaner and enable trimming at the beginning and end of each line. Keep line breaks: each line is one record.
- Copy the result into Full-width / Half-width Converter. Convert to half-width with letters and numbers selected. There is no need to convert punctuation or katakana for this example.
- Copy that result into Duplicate Line Remover. Keep case-sensitive comparison, retain the first occurrence and preserve the original order.
Trimming produces A001 on the first line. Width conversion makes the first two lines identical. Deduplication then produces:
A001 B002
Why deduplicating first can miss a match
Full-width Ａ００１ and half-width A001 are different strings. Ignoring surrounding spaces in Duplicate Line Remover does not normalize character width. That option also affects the comparison key; it does not promise to trim the text of the retained line.
Do not treat a full spreadsheet as a one-column list
This workflow is for one item per line. It cannot decide whether two people with the same name are the same person, or keep adjacent spreadsheet columns aligned after you delete entries from only one column. Preserve meaningful differences in names, punctuation and identifiers by deselecting those conversions.
Before pasting back, check the reduction from three lines to two, the retained values, their order and any leading zeros. Numeric interpretation in Excel can remove leading zeros, so test a small paste into a column intended for text. Tools with autosave may retain a draft on this device; delete saved drafts when using a shared computer.
Zenkaku and hankaku in Japanese forms
Zenkaku means full-width and hankaku means half-width. A form may require half-width Latin letters and digits for a code, but full-width katakana for a name. In Zenkaku / Hankaku Converter, choose only the relevant character types. Converting English letters to full-width does not translate them into kana.
After cleaning a Japanese note, use Character Counter and its Japanese script breakdown to check its length and see separate kanji, hiragana and katakana counts. A character total does not prove that two records match; compare the actual text as well.