33

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
Improve this question

7 Answers 7

Reset to default
21

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.
Improve this answer
1
  • 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, 2017 at 18:58
20

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.

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

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

Improve this answer
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.

Improve this answer
0

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.

Improve this answer
0

It's unfortunate that there is still no built-in split macro.

But these days you can use the textbefore and textafter functions to do the split.

"my:column:to:split"
=TEXTBEFORE(TEXTAFTER($A1,":",1),":",1)
"column"
Improve this answer
-1

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

Improve this answer

You must log in to answer this question.

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