TEXTSPLIT PDF Data: Split Pasted Rows into Columns

TEXTSPLIT splits PDF data pasted into one column by cutting each row at a delimiter and spilling the pieces across columns. For pasted PDF rows, use two spaces as the delimiter with empty pieces ignored, then trim the results, so single spaces inside a description stay put while the wider gaps between columns become breaks.

TEXTSPLIT arguments that matter for pasted PDF rows

TEXTSPLIT takes the text, a column delimiter, an optional row delimiter, a switch to ignore empty pieces, a match mode and a padding value. For PDF rows you mostly need three: the text, the column delimiter and ignore_empty set to TRUE.

Microsoft's TEXTSPLIT reference gives the full syntax as TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]). It says ignore_empty set to TRUE skips consecutive delimiters, while the default of FALSE creates an empty cell for each one. It also says pad_with defaults to #N/A, which matters when rows split into different numbers of pieces. The function is listed for Excel for Microsoft 365 and Excel 2024 on Windows and Mac, so an older desktop copy will show a #NAME? error.

Two practical notes before writing any formula:

• TEXTSPLIT returns text. A piece such as "1,250.00" looks like a number but will not sum until you convert it.
• The delimiter can be an array constant, such as {",",";"}, when a row uses more than one separator.

Can TEXTSPLIT split PDF data on runs of spaces?

TEXTSPLIT can split on runs of spaces if you use two spaces as the delimiter, turn on ignore_empty and wrap the result in TRIM. A gap of five spaces then gives one clean break, and a single space inside "Coffee shop" is left alone.

Why this combination works

When a PDF reader copies a row without tabs, it fills the gaps between columns with several spaces. The number varies from row to row, because it depends on how wide each value was. Splitting on a single space would cut every word apart. Splitting on two spaces cuts only at gaps, but an odd-length gap leaves a stray leading space on the next piece, and an even one longer than two leaves an empty piece.

The formula handles both:

=TRIM(TEXTSPLIT(A2," ",,TRUE))

ignore_empty drops the empty pieces, and TRIM strips the stray leading spaces. Nothing else changes.

Worked example: four pasted rows

These example rows, invented for illustration, came from a statement-style PDF and landed in A2:A5:

With the formula in B2, filled down to B5, each row spills four columns: date, description, amount and balance. "Coffee shop downtown" stays in one cell. To make the last two columns numeric, wrap them: =IFERROR(--TRIM(TEXTSPLIT(A2," ",,TRUE)),TRIM(TEXTSPLIT(A2," ",,TRUE))) converts anything that looks like a number and leaves text as text.

Spilling a whole pasted PDF block with one TEXTSPLIT

One TEXTSPLIT can fill every row and column at once if you first join the pasted lines into one string with a row marker between them. TEXTJOIN does the joining, and TEXTSPLIT's row_delimiter turns each marker back into a new row.

The formula for the example block is:

=TRIM(TEXTSPLIT(TEXTJOIN("|",TRUE,A2:A5)," ","|",TRUE,,""))

Read it from the inside out. TEXTJOIN glues A2 to A5 together with a vertical bar between rows. TEXTSPLIT cuts columns at two spaces and rows at each bar. The final argument, an empty string, is the pad_with value, so a row with fewer pieces gets blank cells instead of #N/A.

Pick a row marker that never appears in the data. A vertical bar is a safe choice for most statements and invoices. A comma is not, because amounts contain commas.

The single-formula version is tidy, but it hides problems. If one row splits into five pieces instead of four, every column after the bad piece shifts for that row only, and the spill gives no warning. Filling a per-row formula down column B makes a bad row easier to see. When the pasted block has no reliable gaps at all, letting Flash Fill learn the split from an example can be quicker than any delimiter.

Why do PDF descriptions with spaces break TEXTSPLIT?

Descriptions break TEXTSPLIT when the reader copied the row with single spaces everywhere, so the gap between columns looks the same as the space between words. In that case no delimiter separates "Card refund" from "12.00", and you need formulas that count pieces from each end instead.

Splitting from both ends

Statement-style rows usually have a fixed number of fields at the start and end, with a description of any length in the middle. Take them from the outside in:

• Date: =TEXTBEFORE(A2," "), everything before the first space.
• Balance: =TEXTAFTER(A2," ",-1), everything after the last space.
• Amount: =TEXTAFTER(TEXTBEFORE(A2," ",-1)," ",-1), the second piece from the end.
• Description: =TEXTAFTER(TEXTBEFORE(A2," ",-2)," "), everything between the date and the amount.

The negative instance numbers count from the right. This works however many words the description has, as long as the amount and balance never contain spaces.

When neither method fits

Some rows have optional fields, such as a reference number printed on only some lines. A delimiter cannot tell an absent field from a present one. That is the point where pasting gives way to a tool that reads the column positions on the page, and how table detection finds column boundaries explains what such a tool sees that a pasted string has lost. Pasted text often keeps link text but drops the target, which is a separate job covered in recovering hyperlink targets from a PDF. If you are unsure whether the PDF or a spreadsheet should be the copy you keep, the differences between a PDF and an Excel file set out what each one is for. For bank statements specifically, FastBankConvert is built to put dates, descriptions and amounts in their own columns without any splitting formula; it has not opened yet, and the get-started page collects sign-ups.