Cleaning Messy E-commerce Vendor Data in Excel

Every store owner knows the feeling: a vendor sends a "final" price list as an Excel file with 2,000 rows, merged header cells, prices that mix currencies and text, and product names in three different capitalizations. If that file goes straight into your catalog, you will spend the next week fixing what should have been cleaned in an hour. This guide gives you a repeatable workflow for turning vendor chaos into a clean, import-ready CSV.

Start by making the file safe to edit

Save a copy and freeze the original. Never clean the only copy of a vendor file. Keep vendor_original.xlsx untouched so you can always start over.
Unmerge all cells. Merged headers look nice but break every downstream step. Select all, unmerge, and fill the resulting gaps.
Promote the real header row. Vendors often put logos, disclaimers and empty rows above the data. Delete everything above the row that actually names the columns, so row 1 is the header.
Remove empty rows and columns. Select all, go to Find & Select → Go To Special → Blanks, and delete. Empty rows become empty products on import.

Normalize the columns you will actually import

You rarely need every column a vendor provides. Create a new sheet called Clean and copy only the columns you will import: name, description, price, SKU, category, inventory, image URL. This is also the moment to rename headers to match your target format exactly — Product Name vs name vs Title matters on import.

Product names: one style, no extra spaces

Use TRIM to remove leading, trailing and double spaces: =TRIM(A2).
Fix capitalization with PROPER or UPPER depending on your catalog style, then paste as values.
Standardize common words: "T-Shirt", "T Shirt" and "tee" should become one form. A simple find-and-replace on the whole column, done once, saves dozens of manual fixes.

Prices: numbers only

Vendor prices are the messiest column. You will see $19.99, 19,99 €, 19.99 USD, 19.990 and Call for price in the same column.

Remove currency symbols and text with Find and Replace.
Replace comma decimals with periods if your store expects 19.99.
Select the price column, set the format to Number with two decimals, and use VALUE to coerce text that looks like a number into a real number: =VALUE(SUBSTITUTE(SUBSTITUTE(B2,"$",""),",","")).
Filter for anything that is still text after coercion — those are the "Call for price" rows that need a decision, not an import.

Categories: build a controlled vocabulary

Vendors rarely match your category tree. One file will have Mens > T-Shirts, Tshirts - Men, Apparel/Men/Tee and MEN for the same thing.

Create a two-column map on a separate sheet: Vendor Category | Your Category.
Use XLOOKUP or VLOOKUP to translate: =XLOOKUP(E2, Map!A:A, Map!B:B, "Uncategorized").
Anything that falls into "Uncategorized" is a new vendor term you need to map — add it to the map and refill. After two or three passes, the column will be consistent.

SKUs and images: uniqueness and reachability

Use Conditional Formatting → Highlight Duplicates on the SKU column. Duplicate SKUs will cause products to overwrite each other on import.
For image URLs, check that the value starts with https://. A broken image URL will not fail an import, but it will leave products imageless.

Validate before you export

Before you save as CSV, run these checks on the Clean sheet:

Every row has a name, price and category.
Prices are numbers, not text.
SKUs are unique.
Image URLs, if present, start with https://.

Save the Clean sheet as CSV UTF-8 (Comma delimited) — not plain CSV — and spot-check the first and last ten rows in a text editor.

The faster path for recurring vendor files

If the same vendor sends a file every month, rebuilding the cleaning steps each time is wasted work. Upload the vendor CSV to CSV Pilot and describe the rules once: "strip currency symbols from prices, map the vendor categories to mine, remove duplicate SKUs, and flag anything missing a price." The agent applies the rules to each new delivery the same way, and the preview shows the result before you export. The spreadsheet workflow above still works — but for repeat deliveries, described rules beat repeated formulas.