Clean a spreadsheet list without losing useful data
A copied spreadsheet column often contains extra spaces, repeated entries and inconsistent ordering. Cleaning it is straightforward when you decide what each value means first. Work through the following sequence with a copy of the original list, then compare the result before putting it back into your spreadsheet or import file.
1. Preserve the meaning of each row
Start with one value per line. Keep the column heading separate from the values, and note the original row count. If the column belongs to a larger table, do not sort or deduplicate it independently and paste it back beside unchanged columns: that can associate one person’s name with another person’s details. These tools are best suited to standalone lists.
Treat product codes, telephone numbers and identifiers as text. For example, 00124 and 124 may identify different records. Excel can remove leading zeros or alter long numeric identifiers during import; set the destination column’s type to Text before importing values that must remain exact.
Further reading: Microsoft: keeping leading zeros and large numbers
2. Clean whitespace before comparing entries
Use the whitespace cleaner to remove unwanted spacing. For an identifier list, surrounding spaces usually have no meaning, so an entry such as “ INV-001 ” should become “INV-001”. Retain the line breaks that separate records. Check the result before using operations that collapse spaces inside a value; spacing can matter in names, addresses, fixed-width records and source code.
Decide what an empty line means. It may be a separator that you can discard, or a missing value that needs attention. Do not let removing blank lines hide an incomplete record. The example below should produce two distinct uppercase identifiers only after you intentionally trim the surrounding spaces.
INV-001
INV-002
INV-001 3. Remove duplicates with an explicit rule
Run the cleaned list through the duplicate-line remover. Decide whether case matters before merging entries: INV-001 and inv-001 are different text, even if your destination treats them as the same identifier. An exact-text comparison is a sensible first pass when the data’s rules are unclear.
Check how many entries disappeared and inspect representative duplicates. Repeated values are not always errors. A list of invoice events can legitimately contain the same invoice more than once, whereas a list of unique invoice identifiers should not. Deduplicate according to the purpose of the list.
4. Sort only when order is disposable
Use the text sorter when an ordered list will be easier to inspect. Alphabetical order and numeric order answer different questions: character-by-character sorting can place 10 before 2. Test a few representative values and choose an available ordering that matches the task.
Preserve the original order when it represents priority, chronology or another meaningful sequence. Sorting does not improve data merely because the result looks tidier.
5. Add formatting, then verify the destination
Add a common prefix or suffix last. For example, prefixing each identifier with “item/” creates item/INV-001 and item/INV-002 without manually editing every row. Check that existing values do not already contain the prefix.
Paste a small sample into the destination first. Confirm row count, leading zeros, punctuation and line breaks, then import the complete list. Keep the untouched original until the destination has accepted the data correctly.