I've got a column where each cell in the column contains two different pieces of data separated by a known delimiter ", ". How can I split each cell in this column around the delimiter?


ABC, 123
FOO, 666
ROFL, 411

I want

-------    -------
ABC        123
FOO        666
ROFL       411

You can also replace the delimiters with tabs:

  1. Copy the cells and paste them to a new TextEdit document.
  2. Replace ", " with tab. You can insert a tab by pressing option-tab.
  3. Copy and paste the text back to Numbers.
  • Don't try to paste into the header row or column. Paste into an ordinary data cell. You can then right click on rows and columns to remove empty headers and/or change data rows and columns into headers. – Bob Wakefield Jan 24 '17 at 18:58

You can do this directly in Numbers.

First, add two empty columns after the column with the data you want to split.

If the first piece of data you want to split is in cell B2 and is separated by a space, then use this formula in the empty cell C2: =LEFT(B2, FIND(" ",B2)) If the data is separated by a comma, then replace " " with ",".

In the empty cell D2, paste this formula: =RIGHT(B2, LEN(B2)−FIND(" ", B2))

If your data is separated by a comma followed by a space, then use this: =RIGHT(B2, LEN(B2)−FIND(",", B2)-1)

Hope this helps.

  • This is what most closely resembles the proper answer for the question above – boliva Jul 28 '15 at 18:48
  • Awesome stuff, thanks so much! Here's to a great 2021 🎉 – Paul Feb 2 at 2:29

Just rename the file in question to end in ".csv" Then ("File" > "Open…") and Numbers.app did the right thing with the data for me.

enter image description here


In Numbers 3 this worked for me by replacing commas with semicolons in the CSV-file:


ABC, 123
FOO, 666
ROFL, 411


ABC; 123
FOO; 666
ROFL; 411

and then simply opening the CSV-file with Numbers.


Under the option for "format" (should be on the top right) scroll to the bottom of the table option. There you will see "adjust import settings" this option will allow you to set deliminators for the columns.

In my case, I needed to separate via a | (bar). Worked perfectly.


Copy the data to a text editor and save it as mydoc.csv . In numbers, open mydoc.csv.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .