How to Clean PDF Data Pasted Into Excel on Mac: Text to Columns, Numbers and Dates
You copied a table from a PDF, pasted it into Excel and got a single column of text, with amounts that refuse to add up and dates that will not sort. That is normal. A PDF stores the position of characters on a page, not rows and columns, so what arrives in Excel is text that only looks like a table. This guide shows how to turn it into real data using tools built into Excel for Mac.
Quick answer: Select the pasted column, open Data > Text to Columns, choose Delimited or Fixed width, and split it. Then convert text to numbers (click the green warning triangle and choose Convert to Number), strip hidden spaces with TRIM and SUBSTITUTE, and set date columns to a date format. Finish by pasting everything as values with ⌃⌘V.
Why pasted PDF data is messy
A PDF is closer to a picture of a page than to a document with structure. Each word or number is placed at coordinates. When you copy from it, the app reconstructs reading order and guesses where the columns were, then puts the result on the clipboard as text. Excel receives that text and does its best, and often its best is one long string per row.
Three side effects follow. Separators between columns are spaces rather than tabs, so Excel cannot see the columns. Numbers carry formatting characters such as currency symbols, thousands separators and trailing minus signs, so Excel treats them as words. And invisible characters, especially non-breaking spaces, ride along and break formulas that look correct.
| What you see | Cause | Fix |
|---|---|---|
| Everything in column A | The PDF text has no tab separators | Data > Text to Columns |
| Numbers will not add up | Values are text, not numbers | Convert to Number, or multiply by 1 with Paste Special |
| Formulas ignore spaces you cannot see | Non-breaking spaces from the PDF | SUBSTITUTE with CHAR(160), then TRIM |
| Negative amounts shown as 123- or (123) | Accounting notation as text | Formula that reads the trailing minus or parentheses |
| Dates will not sort | Dates are text | Text to Columns with a Date column type, or DATEVALUE |
| One entry spread over two rows | Wrapped text in the PDF | Fill down, then combine or delete the extra rows |
| Header repeated every page | Page header copied with the data | Filter and delete those rows |
Step 1: Split the text into columns
Select the column with the pasted data, then open the Data tab and choose Text to Columns. The wizard has three steps.
Choose the data type. Pick Delimited if columns are separated by a consistent character such as a comma, a tab or a run of spaces. Pick Fixed width if the columns line up visually but the gaps vary.
Set the delimiters or break lines. For Delimited, tick the separator that applies, and tick Treat consecutive delimiters as one if there are runs of spaces. For Fixed width, click on the preview to add or move the vertical break lines.
Set the column data format. Choose General for most columns, Text for codes with leading zeros, and Date with the right order (day-month-year or month-day-year) for date columns.
Before you click Finish, check the destination. By default Text to Columns overwrites the cells to the right, so move the data to a blank area or a new sheet first if there is anything you want to keep.
When spaces are both separator and content
A description such as Coffee Shop Main St contains spaces, so splitting on spaces would cut it into four columns. Use Fixed width when the columns line up, or split on a more distinctive character. If the amount is always the last item on a row, TEXTSPLIT (in Microsoft 365) or Flash Fill can peel it off. Flash Fill is ⌃E on a Mac: type the first example of what you want next to the data, then press ⌃E and Excel fills the rest by pattern.
Step 2: Turn text into real numbers
If amounts are left-aligned, or the status bar does not show a sum when you select them, they are text. Excel may flag them with a small green triangle in the corner.
Quick fix for a few cells: click the warning icon and choose Convert to Number.
Quick fix for a whole column: type 1 in a blank cell and press ⌘C, select the amounts, choose Paste Special (⌃⌘V), pick Multiply and click OK. Excel multiplies every value by 1, which forces a conversion. Delete the helper cell afterwards.
Formula approach: =VALUE(B2) converts text that looks like a number. Fill it down and paste the results as values.
If a value will not convert, it contains something Excel cannot read, usually a currency symbol, a space or a stray character. The next step deals with those.
Step 3: Remove hidden spaces and characters
PDF text often contains non-breaking spaces, which look like normal spaces but are a different character, number 160. TRIM does not remove them, which is why a formula that looks identical to one that works returns an error. Clean a cell with:
=TRIM(SUBSTITUTE(A2, CHAR(160), " "))
Wrap that in VALUE if the result should be a number. For other invisible characters, wrap the text in CLEAN as well.
Step 4: Fix negative numbers
Accounting layouts show negatives in two common ways: in parentheses, like (1,250.00), or with a trailing minus, like 1,250.00-. Excel does not always treat either as a negative when it arrives as text. A formula that handles both, assuming the text is in B2:
=IF(RIGHT(B2,1)="-", -VALUE(SUBSTITUTE(LEFT(B2,LEN(B2)-1),",","")), IF(LEFT(B2,1)="(", -VALUE(SUBSTITUTE(SUBSTITUTE(B2,"(",""),")","")), VALUE(SUBSTITUTE(B2,",",""))))
It looks long, but it only does three things: it checks for a trailing minus, then for parentheses, otherwise it reads the number as it is. Paste the formula results as values when you are happy with them. If your region uses a comma as the decimal separator, swap the separators in the formula to match.
Step 5: Fix dates
If you did not set the column type in Text to Columns, dates may be stuck as text. Re-run Text to Columns on just the date column and choose Date in step 3, selecting the right order. Alternatively, =DATEVALUE(A2) converts text that Excel recognises as a date in your regional format. A date such as 03/04/2026 is ambiguous: check against a row where the day is greater than 12 to confirm which order your source uses.
Step 6: Repair wrapped rows and repeated headers
Two layout problems appear in almost every PDF table.
Wrapped text. A long description that wrapped onto a second line in the PDF arrives as a second row with an empty date and amount. Select the date column, use Go To Special > Blanks (see our Go To Special guide), type = and press the up arrow, then press ⌃Return to fill every blank with the value above. Combine the description cells and delete the extra rows.
Repeated headers. Apply an AutoFilter, filter for the header text, select the visible rows and delete them. Our guide to AutoFilter shortcuts on Mac has the keys.
Step 7: Lock in the results
When the data is clean, copy the final columns and paste them as values so no formula depends on the messy original. Our guide to the paste values shortcut shows the quickest keys. Then delete the helper columns and the raw paste, set number formats, and format the range as a table so sorting and filtering are safe. If the file will go on to another system as a CSV, the steps in cleaning a CSV in Excel on Mac apply as well.
Common mistakes
Splitting before checking for spaces inside values. A description with spaces will be torn apart. Use Fixed width or a better delimiter.
Formatting numbers as Number without converting them. Changing the display format does not change the underlying text. Convert first.
Overwriting the raw paste. Keep the original on a separate sheet until the cleaned version is verified against the PDF totals.
Trusting the sum without a check. Compare your total with the total printed on the PDF. If they differ, a row went missing or a negative was misread.
Frequently asked questions
Why does Excel put my whole PDF table in one column?
Because the pasted text has no tab characters, so Excel has no idea where one column ends and the next begins. Text to Columns supplies that information.
Why does my SUM return zero?
The cells are text. Convert them to numbers with the green triangle, Paste Special Multiply, or VALUE.
What is the shortcut for Text to Columns on a Mac?
Excel for Mac has no default key for it. Use the Data tab, or the Help menu search with ⇧⌘/ and type Text to Columns.
Is there a faster way than cleaning by hand?
For a one-off table, usually not. For a table you receive every month, build the cleaning formulas once in a template and paste each new month into it.
Does this work for scanned PDFs?
Only after the text has been recognised. A scanned PDF is an image, and pasting from it gives nothing until it goes through text recognition first.