How to Turn an Image Into Google Sheets Data With OCR in Google Drive

How to Turn an Image Into Google Sheets Data With OCR in Google Drive

Copying data from an image into a Google Sheets file manually can be time-consuming and increases the chances of typos. Luckily, you can extract the information into your spreadsheet in just a few simple steps using the Optical Character Recognition (OCR) tool in Google Drive. Here’s how.

Points to Note Before You Start

Before you begin converting the text in your image to data in Google Sheets, here are some pointers that will help ensure the process works as expected:

  • Make sure your image is clear: The better the quality of the picture, the better the conversion will be. If your image is a screenshot, this shouldn’t be too much of a problem. However, if you capture the picture with your phone or a camera, make sure it’s well-lit and not obscured by shadows. Also, try your best to have a steady hand to avoid blurriness.
  • Use images with high-resolution text: The data in the picture must be at least 10px high.
  • Ensure the image is the correct format: This process works with JPEG, PNG, TIFF, and GIF files. If your image is not one of these image file formats, be sure to convert it first.
  • Don’t use overly large images: Google recommends that the file size be 2MB or smaller.
  • Standard fonts work best: If the text or numerical data in the image is in an unusual font, the conversion might not work as well as you had hoped.
  • Formatting may not be preserved: Be prepared to make manual changes to the data formatting once the conversion is complete.
  • You need a Google account: In order to take advantage of this OCR conversion tool, you need to be signed in to your Google account.

You can also follow the steps in this guide to convert text and numbers in scanned PDFs into data in Google Sheets.

Step 1: Upload the Image to Google Drive

Let’s say you’ve downloaded this image—a screenshot of a PDF containing various names and addresses in PNG format—and you want to extract and edit the data in Google Sheets.

A scanned PDF containing the names and addresses of various fictional characters.

To follow along as you read, download a free copy of this PNG image. After you click the link, you’ll find the download button in the top-right corner of your screen.

Because the file is a PNG image, you can’t simply select and copy the text, as the file doesn’t contain any “real text” data—only image data. You might also encounter the same scenario if you copy data from a website—if it was uploaded as an image, there won’t be any real text to simply copy and paste into Google Sheets.

Instead, upload the image to Google Drive. Before you do this, make sure the image is the right way up.

Related

How to Rotate Images on Windows 11

Rotate photos using the built-in apps like File Explorer, Photos, and Paint.

Then, sign in to Google Drive, and click and drag the file to the relevant Google Drive folder.

An image is clicked and dragged from a computer file to Google Drive.

Alternatively, click “New” in the top-left corner, select “File Upload,” and locate and upload the image you want to convert.

The New button in Google Drive is selected.

Step 2: Open the Image in Google Docs

Now that your image has been uploaded to Google Drive, it’s time to kick the conversion process into action. To do this, right-click the file, hover over “Open With,” and select “Google Docs.” Yes, you ultimately want the data to be in Google Sheets, but this is a necessary step to take first.

The right-click menu on an image file in Google Drive is expanded, and the option to open it in Google Docs is selected.

At this point, Google Docs opens in a new tab, with the original image at the top of the document and the extracted data underneath.

A Google Docs file with an image at the top and the text from the image extracted beneath.

Also, if you head back to the Google Drive folder containing the image, you’ll see that the Google Docs file you just created is saved in the same location with the same name.

A Google Docs file with the same name as a PNG file in a Google Drive folder.

Related

I Paid for Google’s Drive Storage. Here’s Why I Don’t Regret It

The convenience is worth 2 dollars a month.

Step 3: Review the Data and Make Necessary Changes

While OCR is generally accurate, depending on how the data in the original image is laid out, it isn’t always perfect. So, take some time to review the data in the Google Docs file you just created.

In this example, the column headers in the image are inserted in random positions in the list of extracted data, so I’ll remove these for now and add them again later on when I edit the data in Google Sheets.

Name, Address, and Area are highlighted in a list of text items in Google Docs.

Also, towards the end of the list, the addresses of Spongebob, Superman, and Sweeney Todd have been mixed up, so I need to reorganize this information so that the data is easier to work with in the next step.

Some supposedly linked items in a list in Google Docs are not placed next to each other.

Step 4: Copy and Paste the Data Into Google Sheets

Now that the data is correctly ordered and organized, it’s ready to be moved over to Google Sheets. To do this, select the data, right-click anywhere in the selected area, and click “Copy.”

Some text in Google Docs is highlighted, and Copy is selected in the right-click menu.

If you want to insert the data into a new spreadsheet, right-click the white space in the relevant Google Drive folder, and click “Google Sheets.”

The right-click menu in Google Drive is expanded, and Google Sheets is selected.

On the other hand, if you already have the Google Sheets file set up, locate and open it.

Next, right-click the cell where you want the first part of the copied data to be pasted, hover over “Paste Special,” and click “Values Only.” Pasting the data as values only removes all the formatting, including any line-spacing properties automatically adopted by Google Docs, so that you can work with a clean set of data.

The right-click menu is expanded in a cell in Google Sheets, and Values Only is selected from the Paste Special menu.

Step 5: Format the Data

The final step in the image-to-data conversion is to format the data in Google Sheets, so that it appears and behaves exactly how you want it to.

In my case, the list is set in one column, but I want names, addresses, and areas to be in three separate columns.

An unsorted list of names and addresses in column A in Google Sheets.

There are various ways to transform the dataset accordingly, but in my case, I’m going to use the TRANSPOSE function, which turns horizontal data into vertical data (and vice versa).

Related

Convert a Row to a Column (or Backwards) in Google Sheets

Don’t switch your data manually!

If you want to do the same, in cell B2, type:

=transpose(A1:A3)

and press Enter.

A name, address, and area are transposed from three separate rows into three separate columns in Google Sheets.

Now, because a new name appears in every third row, select cells B1 to B3, and double-click the fill handle to duplicate the TRANSPOSE formula in the relevant cells in column B.

Three cells are selected in Google Sheets, and the fill handle in the bottom-right corner of that selection is highlighted.

Next, select all the newly transposed cells, press Ctrl+C to copy them, and press Ctrl+Shift+V to paste the values only. This detaches the data from the original one-dimensional list, meaning you can delete it, safe in the knowledge that you won’t lose any of your transposed data.

Some transposed cells in Google Sheets are selected, copied, and pasted as values.

Now, after right-clicking the column A header and selecting “Delete Column,” you need to remove the blank rows. To do this, select all the columns containing the data, and in the Data tab, hover over “Data Cleanup,” and click “Remove Duplicates.”

Remove Duplicates is selected in the Data tab in Google Sheets.

This will leave one blank row, which you can delete manually by right-clicking any cell in that row, and clicking “Delete Row.”

The right-click menu is expanded in a cell in Google Sheets, and Delete Row is selected from the options.

Finally, to reinsert the column headers, right-click any cell in row 1, click “Insert 1 Row Above,” and type the headers as necessary.

The right-click menu is expanded in a cell in Google Sheets, and Insert 1 Row Above is selected from the options.

You can now convert the data to a formatted Google Sheets table by selecting all the cells (including the column headers), right-clicking the selection, and clicking “Convert To Table.”

Convert To Table is selected in Google Sheets.

Then, click the down arrows in each column header and hover over “Edit Column Type” to ensure the correct data type is assigned to each field, and your converted data is ready for use in your Google Sheets spreadsheet!

The data type of a column of data in a formatted table in Google Sheets is selected as Text.

Related

Google Sheets vs. Microsoft Excel: Which Should You Use?

Yes, they’re both spreadsheet platforms, but they’re actually very different.

Leave a Comment

Your email address will not be published. Required fields are marked *