Paste Special from a PDF to a Spreadsheet

It is so frustrating to look up online the standards for something and to not have it in a spreadsheet format. A nicely organized PDF document is nice for printing but terrible for doing anything with it. Paper or PDF you probably need to by hand type into some sort of lesson planning guide. I am not doing that by hand! This is a handy skill. Try sharing a PDF of standards with your students and asking them to copy them into a spreadsheet and to clean up the data.

At the bottom, I have some resources for providing your students with directions on how to clean up data copied from a PDF into a spreadsheet.

Copy the PDF Text

Highlight the text on the PDF and use Control C to copy. This is going to chop up paragraphs, copy headers and footers, and do other not nice things for when you paste.

Hold Down the Shift Key

One trick when pasting is to hold down the Shift key. In Google Sheets, in particular, this is known as paste special. When pasting from the PDF all of the text is together into one cell, difficult to deal with. Holding down the Shift key when you paste will result in each line of the PDF pasting into a new row on the spreadsheet.

This is a problem in that text that is supposed to be together is not.

Grab and Drag

Single click on a cell and hover your mouse over the edge of the cell until it turns into a hand. Click and hold down. While holding down, drag the cell to another location.

Put Text Back Together

When I copied and pasted the text into the spreadsheet the paragraphs of text were split up. The value of 7.1 and the text for that standard are in two different cells: A1 and B1. The word wrapped sentence was on the second line in B1. I want all of the text in the same row. I singled clicked on cell B1 and hovered the mouse over the top of the cell until it was a grabby hand. Click and hold down and drag that cell over to cell C1.

This has the first standard of text in 3 columns. To put the content of all three cells together into one cell I need to concatenate (smash together) the values. Use the ampersand (&) symbol to put the text together. & means AND. I want what is in the first cell AND what is in the 2nd cell AND what is in the 3rd cell.

However, I also need a space between each of the cells. So I need the first cell AND a space AND what is in the 2nd cell AND a space AND what is in the 3rd cell. All text needs to be in quotations.

=A1&” “&B1&” “&C1

Move the Next Standard

The next standard is in row 3. I want to drag it up to row 2 so I have a list of the standards. The next 4 rows are part of the text for the 2nd standard. I need to drag them up to the second row. Each into the next column so they are in the first five columns. These 5 cells need to be concatenated (smashed) together. Write the formula into the same column, second row, that you wrote the first formula.
=A2&” “&B2&” “&C2&” “&D2&” “&E2

Delete Blank Rows

As I drag and move the standards I am left with blank rows. Highlight the rows and right click to delete the rows.

Continue to Drag Standards

Continue dragging the standards up and dragging additional lines of standards into side by side cells.

Fill Down Square

I had written the formula to concatenate (smash) together the 5 cells in the 2nd row. Single click on that cell with the formula and a fill down square appears in the bottom right hand corner. Hover over the square and it will turn into cross hairs. Click and drag down on the fill down square and your formula will be copied to the rows below.

Copy and Paste Special

Highlighting the list of standards, use Control C to copy. If I paste (Control V) onto a different sheet I will get an error since I am pasting the formulas not the values. To overcome this I hold down the SHIFT key when I paste. This strips the formulas and pastes the values.

Insert Checkboxes

Once I have the values pasted I want checkboxes so I can mark off which standards are covered. In the column next to the standards, highlight the cells and use the Insert menu to choose “Checkboxes.” Boom, checkboxes next to each standard.

Try It Yourself

Try dragging the text around in the spreadsheet and concatenating the values together. <span data-mce-type="bookmark" style="display: inline-block; width: 0px; overflow: hidden; line-height: 0;" class="mce_SELRES_start"></span>

Link to Google Sheets spreadsheet with copied and pasted standards from PDF

Have Students Check Off the Standards

Tracking your own data is powerful for students. Students being able to copy a list of tasks and organize them in a spreadsheet will be very helpful in life. In Google Classroom, or whatever platform you use, consider linking to either a PDF of standards for your subject or the sample spreadsheet of standards. Provide the graphic for the checklist of standards and the video (here is a link to it from Google Drive) and ask students to produce a checklist of the standards.