Fix leading zeros and SKU formatting before you compare price lists
The most common reason a price list lookup says "not found" for an item that is plainly there: the two SKUs are not the same text. One kept its leading zeros and one did not, one has a dash, one has a trailing space. Clean both sides into a match key first.
1. Stop the damage at import
Opening a CSV by double-clicking it lets Excel guess every column's type. A SKU like 000123 becomes the number 123, a part number like 3-10 can become a date, and a long numeric code can be shown as 1.23E+11. Excel also keeps only 15 significant digits, so a numeric code longer than that is changed for good, not just displayed differently.
Instead: Data, From Text/CSV, Transform Data, select the SKU column, set Data Type to Text, Close and Load. In Google Sheets, File, Import, and untick "Convert text to numbers, dates, and formulas". If you paste, format the SKU column as Text first.
2. One match key formula
In a helper column on each list (SKU in A2). It swaps non-breaking spaces (CHAR(160), common in data copied from web pages and PDFs) for normal ones, trims, removes spaces, dashes, dots, underscores and slashes, and uppercases:
=UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(A2,CHAR(160)," "))," ",""),"-",""),".",""),"_",""),"/",""))Excel 365 can do the same more readably with LET:
=LET(s,TRIM(SUBSTITUTE(A2,CHAR(160)," ")),UPPER(REDUCE(s,{" ","-",".","_","/"},LAMBDA(a,c,SUBSTITUTE(a,c,"")))))3. Numeric SKUs that lost their zeros
If the supplier's codes are a fixed width (say six digits), pad the side that lost them. With the key in B2:
=IF(ISNUMBER(--B2),TEXT(--B2,"000000"),B2)If widths vary, strip leading zeros from both sides instead:
=IF(ISNUMBER(--B2),TEXT(--B2,"0"),B2)4. Check the cleaned keys for collisions
Normalising can merge two SKUs that really are different (AB-10 and AB1-0, or 0123 and 123 where both exist). Count each key on its own list; anything above 1 needs a look before you trust a lookup:
=COUNTIF($B:$B,B2)Worked example
Seven SKU pairs as they arrive, old list first. The first key is the formula in step 2; the second also applies the zero-stripping rule in step 3.
| Old raw | New raw | Key old | Key new | Match | Zero-stripped old | Zero-stripped new | Match |
|---|---|---|---|---|---|---|---|
"000123" | "123" | 000123 | 123 | No | 123 | 123 | Yes |
"AB-100 " | "AB100" | AB100 | AB100 | Yes | AB100 | AB100 | Yes |
"ab.200" | "AB-200" | AB200 | AB200 | Yes | AB200 | AB200 | Yes |
"CT 220" | "CT-220" | CT220 | CT220 | Yes | CT220 | CT220 | Yes |
"4471-B" | "4471B" | 4471B | 4471B | Yes | 4471B | 4471B | Yes |
"0045" | "45" | 0045 | 45 | No | 45 | 45 | Yes |
"RX_9/10" | "RX-9-10" | RX910 | RX910 | Yes | RX910 | RX910 | Yes |
A raw lookup matches none of the seven. The step 2 key matches 5. Adding step 3 matches all 7.
The free price list compare tool applies the step 2 normalisation automatically, so AB-100 and ab100 match without a helper column.
Pricecheck takes the supplier's email as it arrives, PDF included, compares it with the last version and sends back only the lines that moved. $49/mo, first month free for early signups. No payment now.