28

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?

Given:

COLUMN1
-------
ABC, 123
FOO, 666
ROFL, 411

I want

COLUMN1    COLUMN2
-------    -------
ABC        123
FOO        666
ROFL       411
19

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
14

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
8

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

3

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

Replacing

ABC, 123
FOO, 666
ROFL, 411

with

ABC; 123
FOO; 666
ROFL; 411

and then simply opening the CSV-file with Numbers.

-1

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 .