Split pasted text into columns in Excel, Numbers, Sheets

You paste data into a spreadsheet and every row lands in column A, commas and all. Nothing is broken: spreadsheets only paste data into separate columns when there’s a tab between the values, and your text has commas, semicolons or spaces instead. Here’s how to split it in Excel on a Mac, Numbers and Google Sheets, and how to avoid the problem next time.

First, find the separator

Look at a few pasted rows and work out what sits between the values:

• Commas come from CSV files and many exports: Oslo,Norway,709000.
• Semicolons are common in exports from countries that use a comma as the decimal point.
• Pipes or other symbols show up in logs and system exports.
• Runs of spaces come from aligned text, such as PDF tables and terminal output. These need the fixed-width option, or a careful split on spaces.
• Single spaces are the hardest, because values like “New York” contain spaces too.

Also check for quotes. In "Smith, Jane",42, the comma inside the quotes belongs to the name, and a good splitter respects that.

Excel: Text to Columns

1. Insert a few empty columns to the right of your data, so the split doesn’t overwrite anything.
2. Select the column that holds the text.
3. Choose Data → Text to Columns. The Convert Text to Columns Wizard opens.
4. Choose Delimited and click Next. (Choose Fixed width for aligned text instead, then click in the preview to place the column breaks.)
5. Tick the separator: Tab, Semicolon, Comma, Space or Other with your own character. Set Text qualifier to " so quoted values stay whole. Tick Treat consecutive delimiters as one for runs of spaces.
6. Click Next to set a format for each column, then Finish.

Step 3 of the wizard is the one people skip, and it’s the most useful. Click a column in the preview and choose:

• Text for codes, ZIP codes and account numbers, so leading zeros survive.
• Date, with the order your data uses, such as DMY for day-month-year. That converts dates correctly even when your Mac uses a different order.
• Do not import column (skip) for columns you don’t need.

Google Sheets: Split text to columns

Google Sheets offers the split right after you paste:

1. Paste the text.
2. Click the paste icon that appears next to it and choose Split text to columns. (Or select the cells and choose Data → Split text to columns.)
3. If Sheets guessed wrong, pick the right character from the Separator menu: comma, semicolon, period, space or custom. Detect automatically is the default.

The split is static: change the original text and nothing updates. For that, use a formula.

Numbers: the workarounds

Numbers doesn’t have a Text to Columns command. There are three ways around it.

The TEXTSPLIT function. Numbers has TEXTSPLIT, the opposite of TEXTJOIN. In the cell next to your text, type:

=TEXTSPLIT(A2, ",")

The pieces fill the cells to the right. To keep the values and drop the formulas, select the results, copy, and choose Edit → Paste Formula Results.

Open it as a text file. Numbers has a proper import dialog for delimited text, but only for files:

1. Paste the text into TextEdit, choose Format → Make Plain Text, and save it as a .txt or .csv file.
2. Drag the file onto the Numbers icon in the Dock.
3. Click the table, then in the Format sidebar click Table and Adjust Import Settings.
4. On the Delimited tab, choose the separators next to Separate Values Using, set a Text Qualifier, and click Update Table.

Apple notes that you can’t adjust the import settings once you’ve edited the table, so get them right first.

Magic Fill. For pattern-based splits, such as first names out of full names, Numbers can fill a column from a couple of examples. It requires an Apple Creator Studio subscription, as of October 2026.

Formulas: TEXTSPLIT and SPLIT

Formulas keep the split live, so new rows split as soon as you add them.

Two Google Sheets details catch people out. By default, SPLIT treats each character of the delimiter separately, so ", " splits at commas and at spaces. And it drops empty values by default, which shifts every following value one column left. Writing =SPLIT(A2, ",", FALSE, FALSE) fixes both.

Excel’s TEXTSPLIT can also split into rows and columns at once. =TEXTSPLIT(A2, ",", ";") turns a,b;c,d into a two-by-two block. In older Excel versions without TEXTSPLIT, use Text to Columns.

Avoid the problem next time

The split is only needed because the clipboard held text without tabs. Get tabs, or a real table, on the clipboard and the paste lands in columns by itself:

• Use the source’s export when there is one, and open the CSV file instead of pasting.
• Copy web tables as tables. A real HTML table copied from a browser pastes into separate columns. See copying a table from a website.
• Capture tables in PDFs and images as tables, not as lines of text.

In Unpixel

When Unpixel captures a table, it puts HTML and tab-separated text on the clipboard together. Excel, Numbers, Google Sheets and Notes paste real cells, so there’s nothing to split. If a capture arrives as plain lines instead, check that Detect tables is on in Settings → Recognition, and select only the table: a selection that is mostly prose is copied as text on purpose.