To get an image to Google Sheets as real cells, first pull the text out of the picture with OCR, for example by opening the image with Google Docs from Drive. Then paste that text into a sheet and split each line into columns. Check the amounts against the photo before you sort or total anything.
Can Google Sheets read a table straight from an image?
Google Sheets in a browser has no menu item that reads a picture and fills cells with its numbers. Insert > Image places the picture in a cell or over the grid, but it stays a picture. The text has to come out through a separate OCR step, and only then does Sheets treat it as data.
OCR stands for optical character recognition: software looks at the shapes in a picture and guesses which letters and digits they are. It is a guess every time, and that matters for money. A photographed receipt total of 48.60 that comes back as 48.80 looks perfectly reasonable in a cell, and nothing in Sheets will flag it.
There are a few places that OCR can happen before the text reaches your sheet:
- Google Drive, by opening the image with Google Docs. This is the route described below, because it is free and already sits next to Sheets.
- Your phone's camera or photo app, many of which let you select text inside a picture and copy it.
- A dedicated converter, which reads the layout as a table rather than as loose lines of text.
Whichever you use, the result arrives in Sheets as plain text first. Everything after that, splitting, converting and checking, happens inside the spreadsheet.
Running an image through Google Drive OCR first
Google Drive can turn a photo into editable text by opening it as a Google Doc. Upload the image, right-click it, choose Open with, then Google Docs. Drive creates a document with the picture at the top and the recognised text underneath, which you then copy into a sheet.
Google's page on converting images and PDFs to text with Google Docs sets out the limits. It accepts JPEG, PNG and GIF images and multipage PDFs, and asks for files of 2 MB or smaller. It also warns that lists, tables and columns are often not detected. That last point is the one to plan around: expect a stream of lines, not a grid. The same route works on scanned PDFs, and the Google Docs OCR guide for PDFs covers what it keeps and drops there.
Steps from photo to text
- Take the photo flat and square to the page, in even light, with the table filling most of the frame.
- Crop away the background and anything outside the table, such as a logo or a handwritten note.
- Rotate the picture so the text is upright. Google's guidance says to do this before uploading.
- Check the file size. A phone photo can be well over 2 MB, so export a smaller copy or a screenshot of it.
- Upload it to Google Drive, right-click it, and choose Open with > Google Docs.
- Scroll past the image in the new document to the text underneath.
Google also suggests text at least 10 pixels high and common fonts. Statement fine print and thermal receipts often fail both tests, so a closer photo of half the table usually does better than a distant photo of all of it. For tips on taking the picture itself, see using your phone as a document scanner.
Pasting OCR image text into a Google Sheets tab
Copy the recognised text from the Doc, click cell A1 of an empty sheet, and paste. Each line of text lands in its own row of column A. That one-column result is normal, and it is the shape the next step expects, so do not try to fix it by hand yet.
Two habits make the paste cleaner. First, paste into a fresh tab, not beside existing data, so a stray line does not overwrite anything. Second, if the Doc shows a heading line, a page number or a "continued" line, delete those rows in Sheets right after pasting. They break the pattern that a split relies on.
Watch for line breaks in the wrong place. OCR sometimes puts a long description on two lines, which gives you two rows where the photo had one. Scan column A for rows that have a description but no amount and merge them with the row above before you split. In a long list, a filter on "rows that do not end in a digit" finds them quickly.
If the text pasted as one huge cell instead of many rows, the Doc probably joined lines with soft breaks. Paste it into a plain text editor first, then copy it again from there.
Splitting photographed rows into Sheets columns
Once each transaction sits on its own row in column A, select the column and choose Data > Split text to columns. Sheets offers separators such as comma, space and custom. Photographed statement lines usually separate on spaces, which is where the trouble starts, because descriptions contain spaces too.
A line like 03/14 COFFEE HOUSE MAIN ST 6.75 split on spaces gives you six cells, not three. The date and the amount end up in the right place only if every description has the same number of words, and they never do. There are two practical ways around that.
Split with formulas instead
Leave column A alone and pull each field out with a formula that looks at the ends of the line:
- Date in B2:
=LEFT(A2, FIND(" ", A2) - 1)takes everything before the first space. - Amount in D2:
=REGEXEXTRACT(A2, "[-\d,]+\.\d{2}$")takes the number at the end. - Description in C2:
=TRIM(MID(A2, LEN(B2) + 2, LEN(A2) - LEN(B2) - LEN(D2) - 1))takes what is left in the middle.
Fill those down and the description keeps its spaces. The post on splitting text to columns in Google Sheets covers the menu route and the SPLIT function in more depth, including custom separators.
Worked example
These three lines are made-up examples of what OCR might hand back from a photo of a short statement:
| OCR line (column A) | Date (B) | Description (C) | Amount (D) |
|---|---|---|---|
| 03/14 COFFEE HOUSE MAIN ST 6.75 | 03/14 | COFFEE HOUSE MAIN ST | 6.75 |
| 03/15 PAYROLL DEPOSIT 1,240.00 | 03/15 | PAYROLL DEPOSIT | 1,240.00 |
| 03/16 HARDWARE 0UTLET 42.l0 | 03/16 | (error until D is fixed) | (no match) |
The third row shows two classic OCR errors in one line: a zero in place of the letter O, and a lowercase L in place of the digit 1. The zero in the description is cosmetic. The amount error is not, and the formula's failure to match is useful because it points you straight at the row. The description formula fails too, since it measures the amount in D, so it comes back once you correct the amount.
Which table photos does OCR misread?
OCR misreads photos where the characters are small, faint, skewed or crowded. Statement and receipt photos are prone to all four. The errors that matter most are digit swaps inside amounts, lost decimal points and minus signs, and columns read in the wrong order, because each one produces a believable number.
The table below lists the patterns that turn up most often and what they look like once they reach Sheets.
| Photo problem | What lands in the cell | How to spot it |
|---|---|---|
| Faded thermal paper | Missing digits, such as 4.60 for 14.60 | Total does not match the printed total |
| Page photographed at an angle | Lines from two columns merged into one | Rows with two amounts |
| Tiny print | 8 read as 3 or 6, 1 read as l or 7 | Amounts that fail a number check |
| Minus sign or CR marker | Sign dropped, so a debit reads as a credit | Running balance stops agreeing |
| Shadows across the page | Whole lines skipped | Fewer rows than the photo shows |
| Two tables in one photo | Both tables interleaved in one list | Headings appear mid-list |
The last row deserves a word. A photo of a page with a summary box beside the transaction list often comes back with both mixed together. Crop to one table per photo. If the source is a PDF rather than a picture, the guide to extracting multiple tables from a PDF shows how to keep them apart.
Handwriting is its own case. OCR tools built for print do poorly on handwritten figures, so a handwritten ledger page is often quicker to type.
Proving the image amounts survived in Sheets
Check the amounts in three ways before you rely on the sheet: make sure each one is a real number, compare the total with a total printed on the photo, and count the rows against the picture. Any one of those can catch an error the others miss.
Make the amounts numeric
REGEXEXTRACT and the split menu both return text when the cell holds commas or stray characters. Wrap the amount formula in VALUE, as in =VALUE(REGEXEXTRACT(A2, "[-\d,]+\.\d{2}$")), or clean the column afterwards. A text amount is left-aligned and SUM ignores it, which makes a total look smaller without any error. The post on Google Sheets numbers stored as text walks through finding and fixing them.
Compare with a printed total
Most photographed tables carry some total: a receipt total, a page subtotal, or the change in balance between the first and last line of a statement. Put =SUM(D2:D40) under your amounts and compare. If it is off, sort the differences: a difference that divides evenly by 9, counted in cents, often means two digits swapped; an error equal to one line's amount means a missing or doubled row.
Count the rows
Count the lines in the photo, then use =COUNTA(D2:D40) in Sheets. Fewer rows means OCR skipped a line, often one under a shadow or a fold.
When retyping an image table beats OCR
Typing the table by hand is faster when it has fewer than about a dozen rows, when the photo is poor, or when it is handwritten. OCR saves time on long, clean, printed tables. On a short or messy one, the cleanup and checking take longer than typing, and typing has no hidden digit swaps.
A rough rule: if you would spend more than a minute correcting each row, stop and type. Retyping also lets you enter dates and amounts in a consistent format from the start, which removes the conversion step.
There is a third option when the picture is of a bank statement. If you can download the statement as a PDF from the bank instead, do that, because a digital PDF holds the text itself and needs no guessing. FastBankConvert is being built for that job: it reads a statement PDF into date, description, amount and balance columns and warns you when balances do not add up. It is getting ready to open, and you can join through Get started to hear when trial conversions begin.