How to clean messy carrier and supplier data in Excel

Files from carriers and suppliers are rarely clean. Extra spaces, dates that Excel does not understand, numbers that act like text. Here are the fixes I use most.

1. Remove extra spaces and hidden characters

If your cell is A2, put this in a helper column:

=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

What it does, from the inside out:

• SUBSTITUTE(A2, CHAR(160), " ") replaces the non-breaking spaces that come from web pages and some systems. A normal TRIM cannot remove these.
• CLEAN removes hidden non-printing characters.
• TRIM removes leading and trailing spaces, and reduces double spaces to one.

Copy the helper column, then paste it back over the original with Paste Special, Values.

2. Numbers stored as text

If the number is left-aligned or has a small green triangle, it is probably text. Two quick fixes:

• Select the cells, click the warning icon, and choose "Convert to Number".
• Or use a formula: =VALUE(TRIM(A2))

3. Dates that do not behave

If a date like 15/03/2026 is read as text, you cannot sort or calculate with it. Try:

=DATEVALUE(A2)

Format the result as a date. If that gives an error, your computer's date settings may not match the file (day/month versus month/day). In that case use Data, Text to Columns, choose Delimited, click through to the last step, set the column type to Date, and pick the order, such as DMY.

4. Split one column into several

Suppose a column holds "Dubai - Mumbai" and you want origin and destination separately. Use Data, Text to Columns, choose Delimited, and tick Other with a dash as the separator. Or try Flash Fill: type the first result by hand in the next column, then press Ctrl+E.

5. Remove duplicates safely

Select your data and choose Data, Remove Duplicates. Pick only the columns that define a duplicate, such as the shipment number. Always work on a copy of the sheet first, because this cannot be undone after you save.

A habit that saves hours

Never clean the original file. Keep the raw file untouched, and do your cleaning on a copy or with Power Query. Then you can always go back. When you are ready to automate this, see Power Query for monthly files.