# Split Text to Columns in Google Sheets for PDF Data

- Author: FastBankConvert (https://fastbankconvert.com)
- Published: 2026-10-17
- Category: Google Sheets, Numbers and LibreOffice
- Canonical: https://fastbankconvert.com/blog/split-text-to-columns-in-google-sheets

> Split text to columns in Google Sheets when PDF data lands in one column: the menu option, custom separators, and the SPLIT function.
To split text to columns in Google Sheets, select the column that holds the pasted PDF lines and choose Data, then Split text to columns. Sheets guesses a separator, and you can pick a different one from the Separator menu. For a split you can redo or adjust, the SPLIT function does the same job in a formula.

## Why does pasted PDF text pile into one Google Sheets column?

Pasted PDF text lands in one Google Sheets column because a PDF stores words at positions on a page, not cells in a grid. When you copy a statement from a PDF viewer, the clipboard receives plain lines of text. Sheets puts each line in its own row, all in column A.

Each row now holds a whole transaction as one string, such as "03/14 COFFEE HOUSE DOWNTOWN 4.75". The gaps between date, description and amount came from spacing on the page, and copying usually turns them into single spaces or tabs. Tabs are the lucky case, because Sheets often splits tab-separated text on paste. Single spaces are the common case, and they need a split.

A statement that was scanned rather than downloaded has a second problem: the text may come from character recognition and arrive as loose lines with no table shape at all. [Turning scanned text into spreadsheet rows](/blog/scan-text-to-excel) covers that harder case.

## The Split text to columns menu option in Google Sheets

The Split text to columns command in Google Sheets lives in the Data menu. Select the cells you want to split, then choose Data, then Split text to columns. Sheets spreads each cell across the columns to its right, using a separator it detects on its own.

The steps, as [Google Docs Editors Help describes splitting text into columns](https://support.google.com/docs/answer/6325535), are short:

1. Select the column or range that holds the pasted lines.
2. Click Data, then Split text to columns.
3. A small Separator menu appears near the data. Leave it on Detect automatically, or open it and choose another character.

Before you run it, make sure the columns to the right are empty. The split writes over whatever sits there, so a stray note in column C would be replaced. Inserting a few blank columns first is a cheap precaution.

## Picking a custom separator for statement lines in Sheets

A custom separator in Sheets is any character you type into the Separator menu in place of the detected one. For statement lines, the best choice is the character that appears between fields and nowhere inside them. Commas, tabs and semicolons are good when present. A plain space is almost never safe, because descriptions contain spaces too.

Here is a worked example with three example lines pasted from a PDF statement. The values are made up for illustration.

| Example pasted line | Separator chosen | Columns produced |
| --- | --- | --- |
| 03/14;COFFEE HOUSE DOWNTOWN;-4.75 | Semicolon | 03/14, COFFEE HOUSE DOWNTOWN, -4.75 |
| 03/15,PAYROLL ACME INC,1500.00 | Comma | 03/15, PAYROLL ACME INC, 1500.00 |
| 03/16 GAS STATION 12 -38.20 | Space | 03/16, GAS, STATION, 12, -38.20 |

The first two lines split into exactly three columns because each field sits between a separator that never appears inside a description. The third line shows the trap: splitting on a space breaks the merchant name into three pieces and leaves the amount in a fifth column. The last section of this post shows how to handle that line.

If commas appear inside amounts, such as 1,500.00, do not split on a comma. The thousands separator would cut the number in half.

## How does the SPLIT function differ from the Split text to columns menu?

The SPLIT function differs from the menu because it leaves the original text alone and writes its results with a formula. You keep column A intact, and if the separator was wrong you edit one formula instead of undoing a paste. The menu changes the cells in place and is faster for a one-off cleanup.

The syntax, from the [Google Docs Editors Help page for the SPLIT function](https://support.google.com/docs/answer/3094136), is SPLIT(text, delimiter, split_by_each, remove_empty_text). The last two arguments are optional and both default to TRUE. With an example line in A2, a formula in B2 splits it on semicolons:

```
=SPLIT(A2, ";")
```

Two defaults matter for statement text:

- **split_by_each** treats every character in the delimiter as its own separator. A delimiter of " -" splits on spaces and on hyphens, not on the pair. Set it to FALSE when you want to split only on the full string.
- **remove_empty_text** treats two separators in a row as one. Set it to FALSE if an empty field between two separators should keep its own column, such as a missing reference number.

Results from SPLIT are still text. A split date such as 03/14 or an amount such as -4.75 may need converting before it behaves like a date or a number. The guides to [fixing dates in Google Sheets that arrive as text](/blog/fix-dates-in-google-sheets) and to [Google Sheets numbers stored as text](/blog/google-sheets-numbers-as-text) pick up from here.

## Merchant names that scatter when Sheets splits on spaces

Merchant names scatter when Sheets splits on spaces because every word of the description becomes its own column. A line with a three-word payee comes out wider than a line with a one-word payee, so the amounts end up in different columns on different rows. Totals then pick up the wrong cells.

The fix is to pull off the fixed parts and leave the description as the remainder. On most statements the date is the first word and the amount is the last. With an example line in A2:

```
=LEFT(A2, FIND(" ", A2) - 1)
=REGEXEXTRACT(A2, "\S+$")
=REGEXEXTRACT(A2, "^\S+\s+(.*)\s+\S+$")
```

The first formula returns everything before the first space, which is the date. The second returns the last run of non-space characters, the amount. The third returns what lies between them, so "GAS STATION 12" stays in one cell no matter how many words it has.

Check the result before you trust it. Sort by the amount column and look at the top and bottom few rows. A word in the amount column, or a number in the description, points to a line with a different shape, such as a reference number at the end. Those rows are easier to fix by hand than to chase with a cleverer formula.

If you would rather not split pasted text at all, a converter that returns the statement already in columns removes this step. FastBankConvert is being built to give back a CSV or Excel file with dates, descriptions and amounts in separate columns, and you can [ask to be told when it opens](/get-started).