To convert a PDF price list to Excel cleanly, import it rather than paste it, set the product code column to Text before loading so leading zeros survive, turn prices with currency symbols into plain numbers, and fill category headings down into their own column. Then compare a few rows and the item count with the PDF.
Which price list fields break when a PDF becomes a sheet?
Four fields break most often when a PDF price list becomes a sheet: product codes, prices, pack sizes and category headings. Codes lose leading zeros or turn into scientific notation. Prices with currency signs arrive as text. Pack sizes merge into descriptions. Category headings become stray rows with no price.
A price list looks like a simple table, which is why its problems surprise people. The supplier designed it to be read by a buyer, not loaded into a system. Headings break the list into sections, codes are printed exactly as the warehouse uses them, and prices carry the currency. Each of those choices is fine on paper and awkward in a cell.
Here is an invented example of five lines as they appear on the PDF page:
FASTENERS
00412 Hex bolt M8 zinc, box of 50 €12,40
00413 Hex bolt M10 zinc, box of 50 €15,75
HINGES
104520318876543 Steel hinge 75mm, pair €1.234,50
And here is what a careless paste often produces:
| A | B | C |
|---|---|---|
| FASTENERS | ||
| 412 | Hex bolt M8 zinc, box of 50 | €12,40 |
| 413 | Hex bolt M10 zinc, box of 50 | €15,75 |
| HINGES | ||
| 1.04520E+14 | Steel hinge 75mm, pair | €1.234,50 |
Every column is damaged. The rest of this post fixes them one at a time. The last code is long on purpose, to show the second kind of code damage.
Setting product code columns to Text before the price list lands
Product codes need the Text format before the data arrives, because Excel changes them the moment they land. Microsoft's article on keeping leading zeros and large numbers says Excel removes leading zeros and shows large numbers in scientific notation by default.
The same article gives the limit that explains the long code: Excel keeps 15 significant digits, so a 16-digit code loses digits after the fifteenth, and they become zeros. Formatting the cell afterwards does not bring them back, because the original digits are gone. The format has to be set first.
Three ways to set it first:
- Paste into Text cells. Select the empty column where codes will go, press Ctrl+1, choose Text, then paste. Microsoft notes that this does not change values that are already entered, so do it before the paste.
- Import with the type set. If you bring the price list in through Power Query, from a PDF or from a CSV, set the code column's data type to Text in the editor before you load. Importing a PDF into Excel with Power Query shows where the editor opens.
- Use a converter that outputs CSV, then import the CSV with From Text/CSV and set the code column to Text in the same way.
A custom number format such as 00000 can make 412 display as 00412 again, but it works only for codes of a fixed length and of fewer than 16 digits, per Microsoft's article. Treat it as a patch for a sheet that is already damaged, not as the plan.
Codes that look like dates or fractions
Leading zeros are not the only damage. Codes with dashes or slashes can be read as dates. A part number printed as "3-12" may arrive as 12 March or 3 December, depending on your regional settings, and "1/4" may become a date or the fraction 0.25. Excel makes that choice silently, and the cell then shows a date format you never asked for. Once it has happened, the original text is hard to recover, because the cell now holds a date serial number. The same rule applies as for zeros: set the column to Text before the codes arrive. If a list mixes number-only codes with dashed ones, that is one more reason to set the whole code column to Text rather than fixing rows one by one.
Checking codes after the load
Sort the code column and look at the top and bottom. Codes that start with zero should sort with their zero showing. Then search the column for "E+", which catches any code that slipped into scientific notation.
Currency symbols and decimal commas in PDF price columns
A price like "€1.234,50" is text to Excel unless your system uses the same number style, because it mixes a currency sign, a dot for thousands and a comma for decimals. The fix is to remove the symbol and tell Excel which separators the price list uses, so 1.234,50 becomes the number 1234.5.
In Excel, Data, Text to Columns has an Advanced button in its last step where you set the decimal and thousands separators for that column. Power Query offers the same control through the column's Change Type, Using Locale option: pick a locale such as German or French, and the comma is read as the decimal mark.
Remove the currency symbol first. A plain Find and Replace of "€" with nothing does it. If the list mixes currencies, do not strip them blindly. Add a Currency column, fill it from the symbol, then strip.
A check that catches separator mistakes
After converting, sort the price column from largest to smallest. A price list for bolts and hinges that suddenly contains a 123,450 item is a sign the separators were read the wrong way round. Compare the top three prices with the PDF.
Turning price list category headings into a real column
Category headings such as "FASTENERS" belong in a column, not on rows of their own. Move each heading into a new Category column next to the rows below it, fill it down until the next heading, then delete the heading rows. Every product then carries its category, and filters and pivot tables can use it.
In plain Excel, the quick method is a helper formula. With headings in column A and prices in column C:
- Insert a new column at the left and call it Category.
- In the first data row, use a formula that takes column A when column C is empty, and otherwise repeats the cell above. For example,
=IF(D2="",B2,A1)once the columns have shifted right by one. - Fill the formula down the whole list.
- Copy the column and paste it as values.
- Filter the price column for blanks and delete those rows, which are the old heading lines.
In Power Query, the same job is shorter: add a conditional column that copies the heading text when the price is null, use Fill Down on it, then filter out the rows with no price.
The cleaned table for the example looks like this:
| Category | Code | Description | Pack | Unit price |
|---|---|---|---|---|
| Fasteners | 00412 | Hex bolt M8 zinc | box of 50 | 12.40 |
| Fasteners | 00413 | Hex bolt M10 zinc | box of 50 | 15.75 |
| Hinges | 104520318876543 | Steel hinge 75mm | pair | 1234.50 |
Are pack sizes and units in a price list one column or two?
Pack sizes belong in their own column, separate from the description and the price. "Hex bolt M8 zinc, box of 50" mixes what the item is with how it is sold. Split them, so that you can compare prices per unit and match the list against your own stock system.
When the pack size always follows a comma, Text to Columns with a comma delimiter separates it in one pass. When it varies, look for a pattern, such as "box of", "pack of" or "per". A formula using SEARCH can find the phrase and split the text there.
Once Pack is a column, a price per piece is easy to add. For "box of 50" at 12.40, a column with the pack count of 50 and a formula of price divided by count gives 0.248 per bolt. That comparison is often the reason someone wanted the price list in Excel in the first place.
Price lists that are really grids, with sizes across the top and prices in the cells, need reshaping into one row per item and size. The same unpivot technique used for timetables applies, and reshaping a PDF schedule grid into rows explains it step by step.
Supplier price lists you should not convert from PDF
Some price lists are not worth converting. If the supplier can send a CSV or spreadsheet export, ask for it; their system already holds the clean data. If the PDF is a scan, every code risks a misread digit. And if the list changes every week, converting each new PDF by hand will cost more than the supplier's file.
When you must convert a scanned list, treat every code as suspect. Text recognition can read 0 as O, or 1 as l, and the result still looks like a code. Converting a scanned table to Excel covers how to check recognition output.
If the conversion runs on a machine without Microsoft Office, the steps above still apply in LibreOffice Calc, which has its own text import with column types. Converting PDF to Excel on Linux describes that route.
FastBankConvert is built for a narrower kind of PDF table, bank statements, rather than price lists. It is getting ready to open, and its homepage explains what it will do.