Paste a table from a screen or PDF into Numbers on Mac

Numbers is happy to take a table from the clipboard, as long as the clipboard actually holds a table. The trouble starts with tables that only exist as pixels or PDF text: a price list in a screenshot, a timetable in a PDF, figures on someone’s shared screen. Here’s how to get those into Numbers as proper cells, and what to set once they’re there.

Why tables land in one column

A plain copy from a PDF, and Live Text in Preview, both give you lines of text with spaces between the values. Paste that into a table and each line becomes a row, all in one column. Unlike Excel and Google Sheets, Numbers has no Text to Columns command, so the free fixes are workarounds:

• TEXTSPLIT. =TEXTSPLIT(A2, " ") spreads one line across the cells to its right. Values with spaces in them, like “New York”, split too.
• Import as a file. Save the text as a .txt or .csv file, open it in Numbers, and click Adjust Import Settings in the Format sidebar’s Table tab to choose the separators. Do it before you edit the table; after that, the button is gone and you have to import again.
• One column at a time. For a small table in a PDF, hold ⌥ in Preview and drag over a single column to copy just that column.

Splitting pasted text into columns explains each of these step by step. The easier route is to copy the table as a table in the first place, with tabs between the values. Then Numbers needs no help.

Paste as a new table

1. Capture or copy the table, header row included.
2. In Numbers, click an empty area of the sheet so that nothing is selected.
3. Press ⌘V. Numbers creates a new table sized to the data.

A new table is the safest choice for a first look: nothing gets overwritten, and you can check the rows before you move them into a table that formulas depend on.

Paste into an existing table

Click the cell where the top-left value should go and press ⌘V. The paste replaces whatever sits in the cells below and to the right, so make room first:

• Add rows at the end. Drag the Add Row button in the table’s bottom-left corner down to add several empty rows at once.
• Add rows in the middle. Select as many rows as you need, click the arrow next to a row number and choose Add Rows Above or Add Rows Below.
• Insert instead of overwriting. Paste into a separate scratch table, check it, copy its rows, then select a row in the main table and choose Insert → Copied Rows. Numbers adds new rows for them.

Header rows

A captured header row comes in as an ordinary first row: no bold, no repeated header, and Numbers treats it as data when you sort. Turn it into a real header:

1. Click the table.
2. Open the Format sidebar and click the Table tab.
3. Choose 1 from the header rows pop-up menu under Headers & Footer.

Adding a header row converts the existing first row, so its labels stay. A table can have up to five header rows, which helps with two-level headings. Choose Freeze Header Rows from the same area to keep the labels visible as you scroll.

Numbers, dates and codes

Cells start with the Automatic data format, so Numbers decides as it pastes. Prices and quantities become numbers, which is what you want. Text in a date-like pattern, such as 6/10/17, becomes a date. The day and month are read in the spreadsheet’s region order, which follows your Mac’s region unless you change it under File → Advanced → Language & Region. Codes with leading zeros are at risk too.

To control it:

1. Before you paste, select the columns that will hold codes or dates and set their format in the Format sidebar’s Cell tab, under Data Format. Choose Text for codes and IDs.
2. Paste with Edit → Paste and Match Style. Apple says the pasted cells then adopt the formatting of the new location.
3. Compare a few values with the source: a zero at the start of a code, a date that only makes sense one way round, such as 25/12.

In Unpixel

Unpixel recognizes the table on your Mac and puts it on the clipboard as an HTML table plus tab-separated text, the two forms Numbers reads as cells. Press ⇧⌘2, drag over the table and let go. The confirmation says “Table copied” with the shape, such as “6 rows × 3 columns”, before you even switch to Numbers.

• Tables in images and scans. It works on pictures and scanned pages, which no PDF copy command can reach. Use Open Image… for a saved file or a PDF of up to 50 pages.
• Translate as you copy. Hold ⌥ as you let go and each cell is translated into your language. It still pastes as cells, and numbers and prices stay untouched.
• Copy it again later. Every table stays in your local history. Click it to copy the cells again, or right-click it and choose Copy as CSV if you’d rather open it with Numbers’ import settings.