Split Column in Numbers When PDF Data Sticks

To split a column in Numbers when pasted PDF data sticks together, use text formulas: TEXTBEFORE and TEXTAFTER pull out the parts before and after a separator, and TEXTSPLIT spreads a cell across columns. For a whole sheet, save the column as a text file and reopen it with Numbers import settings, which split on any delimiter you choose.

Is there a Text to Columns button in Numbers?

Numbers does not document a Text to Columns command like the one in Excel or Google Sheets. Apple's Numbers guide offers two tools instead: text functions that split a cell with a formula, and import settings that split a text file as it opens. Both work, and they suit different amounts of data.

The formula route keeps the original column untouched, so you can see every split next to its source and fix one row at a time. The import route rebuilds the whole table in one go but gives you nothing to edit row by row. A good rule of thumb: formulas for a few hundred rows of consistent lines, import settings when the separator is clean and the column is long.

A typical stuck cell after pasting a PDF statement looks like this example: "03/14 COFFEE HOUSE DOWNTOWN -4.75". The date, the description and the amount share one cell, separated only by spaces.

TEXTBEFORE and TEXTAFTER formulas to split a Numbers column

TEXTBEFORE and TEXTAFTER split a Numbers column by returning the text on one side of a separator. TEXTBEFORE gives everything before it, and TEXTAFTER everything after. Apple's Formulas and Functions Help page for TEXTBEFORE gives the syntax as TEXTBEFORE(source-string, search-string, occurrence).

The third argument is what makes these functions useful for statements. It is optional and defaults to 1, the first match. A negative number counts from the end, so -1 means the last match. That lets you cut the date at the first space and the amount at the last space, whatever sits between them.

With an example line in cell A2:

=TEXTBEFORE(A2, " ")
=TEXTAFTER(A2, " ", -1)
=TEXTBEFORE(TEXTAFTER(A2, " "), " ", -1)

The third formula takes everything after the first space, then everything before the last space of what remains. The description stays whole whether it has one word or five. Apple's page notes that the functions return an error when the search string is not found, so a blank row or a line with no spaces will show an error. That error is useful: it points to a row worth looking at.

Splitting a whole Numbers cell at once with TEXTSPLIT

TEXTSPLIT splits a whole Numbers cell at once, spilling each piece into its own column. According to Apple's TEXTSPLIT reference in Formulas and Functions Help, it takes the string, a column delimiter, an optional row delimiter and options for empty values and case.

For a line with fields separated by semicolons or tabs, a single formula does the job:

=TEXTSPLIT(A2, ";")

TEXTSPLIT is the right tool when the separator never appears inside a field. On a line separated only by spaces, it breaks the merchant name into one column per word, so "COFFEE HOUSE DOWNTOWN" fills three columns and pushes the amount further right than on a row with a one-word payee. For space-separated statement lines, the TEXTBEFORE and TEXTAFTER pattern above is safer.

Whichever function you use, the results are text. A split amount such as -4.75 may need wrapping in VALUE before it sums, and a split date such as 03/14 still needs a year and a date format.

The CSV round trip that splits a column in Numbers

The CSV round trip splits a column in Numbers by letting the importer do the work. Copy the stuck column into a plain text file, save it, and open that file in Numbers. Then use the Delimited import settings to choose the separator. Numbers rebuilds the table with each field in its own column.

The steps, using the options in Apple's guide to importing an Excel or text file into Numbers on Mac:

1. Copy the column from Numbers and paste it into TextEdit. Choose Format, then Make Plain Text, and save the file with a .txt or .csv name.
2. Drag the file onto the Numbers icon in the Dock.
3. Click the new table, open the Format sidebar, choose the Table tab and click Adjust Import Settings.
4. On the Delimited tab, tick the separator, or type a custom one. Select the option to treat consecutive delimiters as one if fields are padded.
5. Click Update Table, and check the result before you edit anything. Apple notes that import settings cannot be adjusted after you edit the table.

The round trip has the same weakness as TEXTSPLIT: a space separator splits descriptions. It works best when the paste kept tabs between fields. The same importer logic exists in LibreOffice, and the LibreOffice Calc CSV import dialog offers a preview of the columns before you commit, which some people find easier.

When should you stop splitting the column in Numbers?

Stop splitting the column in Numbers when the lines do not share one shape. If some rows carry a reference number, some wrap onto a second line and some have the amount in a different place, every formula needs an exception. At that point, converting the PDF again into proper columns is faster than repairing a paste.

Signs that a paste is not worth rescuing:

• Descriptions are split across two rows, with the amount on the second row only.
• Debits and credits came from two separate columns but now sit in one cell with no sign to tell them apart.
• Page headers and running totals are mixed into the transaction rows every few lines.
• The table is wider than three or four fields, as on the many line types of a business bank statement in Excel.

In those cases, start again from the PDF. Getting a PDF into LibreOffice Calc is one free route on a Mac, though it still leaves cleanup to do. For bank statements, FastBankConvert is being built to hand back a CSV or Excel file with the columns already separated, which Numbers opens directly; join the list on the get started page.