How to clean a list
- Paste your list in the box, or drop a text or CSV file on it. One value per line.
- Read the line under the counters. It says exactly what was removed, for example 14 non-breaking spaces and 3 zero-width spaces.
- If any value needs a look, it is listed with its line number and the reason. Those values are never changed.
- Choose how you will paste the result into Excel, then copy it or save it as a text file.
Hidden characters are the usual reason a lookup says "not found" for two values that look the same. A non-breaking space at the end of a cell is invisible, and Excel's TRIM leaves it there. Cleaning the list first fixes the lookup, the list comparison and the duplicate check in one go.
Why Excel removes zeros and changes long numbers
Excel treats a column of digits as numbers, and a number has no leading zeros. 00123 becomes 123 the moment it is pasted. Long IDs fare worse. Microsoft's page on keeping leading zeros and large numbers says "Excel has a maximum precision of 15 significant digits". Every digit after the fifteenth turns into a zero. An IMEI has 15 digits and survives. A SIM card number (ICCID) has 19 or 20 and loses its last four or five.
Excel may also show a long number in scientific notation, such as 8.61E+14. Once a value has been through that, the original digits cannot be worked out again from the sheet. This page marks those values, and values of 16 digits or more that end in zeros, because both are signs of the same damage. The fix is to export the list again from the system it came from, as text.
Paste the result back without losing zeros
Only a cell that is already text keeps every digit. There are three reliable ways to get there.
- Format the column as Text first. Select the column, set the number format to Text, then paste. Microsoft's page says this does not change numbers that were already entered, so do it before the paste. Choose plain text above.
- Text formulas. Choose Excel text formulas and each value that Excel would turn into a number, date or formula is written as a formula that returns the text, for example
="00123". Paste it into any column. To keep plain text, copy the column and use Paste Special, Values. If you save the sheet as a CSV, check the digits before you open that file again by double-clicking it. - Import the file as text. Save the result with the button below, then in Excel choose Data, From Text/CSV, Transform Data and set the ID column to Text before you load it. Opening the file by double-clicking skips that choice.
An apostrophe in front of a value also works when you type it into a cell. It is not a good fit for a pasted list, so this page does not add one.
Formulas for a list that is already in Excel
These work on text in a cell. If the cell already holds a number, digits past the fifteenth are gone and no formula brings them back.
=SUBSTITUTE(A2,CHAR(160),"")Removes non-breaking spaces. Wrap it as =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160),""))) to remove control characters and extra spaces too.
=TEXT(A2,"000000")Shows a number with at least 6 digits, adding zeros at the start. The result is text.
=SUBSTITUTE(SUBSTITUTE(A2," ",""),"-","")Removes spaces and dashes from inside an ID.
Check digits on IMEI and SIM numbers
The last digit of a 15-digit IMEI is worked out from the other 14 with the Luhn formula. SIM numbers follow the same idea. If the check fails, a digit was mistyped, dropped or changed on the way. Some 20-digit SIM numbers do not carry a check digit, so treat a failure on those as a prompt to look, not a verdict.
Once the list is clean, send the numbers through the phone number formatter if they are phone numbers.
Questions
Why does Excel remove the zeros at the start of my IDs?
Excel reads a column of digits as numbers, and a number has no leading zeros, so 00123 becomes 123. Format the column as Text before you paste, or choose Excel text formulas as the output here.
Why do the last digits of my IMEI or SIM number show as zeros?
Excel keeps 15 significant digits in a number and replaces the rest with zeros. A 19-digit SIM number loses its last four digits. Once that has happened the digits cannot be recovered from the sheet.
Can I get back a number that shows as 8.61E+14?
No. The short form keeps only the first few digits. Export the list again from the source system and open it as text. This page marks these values so you can find them, and it never changes them.
What does the check digit test tell me?
The last digit of an IMEI is calculated from the others with the Luhn formula. If the test fails, a digit was mistyped or lost. If it passes, the number is well formed, which does not prove the device exists.
Is my list uploaded anywhere?
No. The cleaning happens in your browser and nothing you paste is sent to a server, saved or put in the address. Only your option choices are remembered on your device.
If the same exports arrive broken every week, Mbule.Tech can build a tool that cleans them the way your systems expect.