I'm currently using the default Numbers application to open my CSVs. My current use case involves a file that I had to use a different type of separator (|). Is there any way to change the separator? Right now when I open the file it just puts the entire row in one column.

6 Answers 6


In Numbers you can use any separator when importing csv tables.

On the format sidebar, in the table tab, look for adjust Import settings. Inside it you have delimited options where you can even use custom delimiters

  • 2
    It's worth noting that this option is only shown when a CSV is already open, and it won't appear for non-CSV files :o) Commented Apr 18, 2019 at 14:16
  • 2
    no such option in Numbers 10.1 (6913)
    – DataGreed
    Commented Jul 29, 2020 at 14:07
  • 3
    Okay. for this to work you have to click on any cell for this options to appear.
    – DataGreed
    Commented Jul 29, 2020 at 14:11
  • What do I do when I want to change the delimeter while exporting the file? Right now the way you suggested just changes the actual table that's open but I want to save with a different delimeter. Commented Feb 18, 2022 at 11:52

Use sed; the Stream EDitor from Terminal.

sed -i -e 's/"|"/|,"/g' filename

s is used to replace the found expression "|" with ","

g is for "global", meaning do this to the whole line. If you omit the g option it will only replace the first instance, ignoring the rest

-i option is to "edit in place" meaning it changes the file itself.

-e option indicates the expression/command to run.

  • 1
    While this is very helpful if I were to convert the format, it does not help my current use case. I'm asking specifically to be able to use a different separator because my data contains commas. Thanks though, love how you explained what each part of the command helps do (plus I hadn't heard of sed before)
    – Tom Prats
    Commented Jan 9, 2017 at 14:20
  • It's not converting the format, it's still a CSV except that it's using a different separator character
    – Allan
    Commented Jan 9, 2017 at 14:35
  • That's what I meant by format. The separator is the problem here
    – Tom Prats
    Commented Jan 9, 2017 at 14:36
  • 2
    That's a limitation of Numbers. The only option is to work with your data as a CSV file then covert the separators using sed through Terminal or use Excel Online to save it as a CSV with your preferred format.
    – Allan
    Commented Jan 9, 2017 at 14:42
  • open your csv file in Numbers

  • You should see a Adjust Settings thing pop up, click it

  • You'll see an Import Settings dialog make sure you click on Delimited

  • in the Custom Delimiter field add |

  • Click Update Table

  • Adjust Settings

enter image description here

  • But what can we do once the pop-up disappear? Commented Feb 21, 2023 at 16:16
  • @MemphisMeng Click anywhere in the table and then click on "Format" (top right) and then scroll down to the bottom, you'll find "Adjust Import Settings..." Commented Apr 8, 2023 at 9:16

Another option, you can open it in OpenOffice/LibreOffice. It lets you pick exactly what the delimiters are. Then once it opens, save a copy in some other format, then you can open it in Pages.


Using the -i option for sed is courageous, but also dangerous. I would recommend the following:

sed -e 's/"|"/|,"/g' filename > tmp

Then, check that the file tmp is formatted as you wanted it to be. If it is ok, then you can replace the original file with tmp:

mv tmp filename

Numbers >=5.0 after opening a csv-File you get a floating dialog "Table data was imported. Adjust Settings". When hitting "Adjust settings" you can control the delimiters. Found this solution in this tutorial

  • This answer was already provided in the accepted answer. To avoid downvotes you might want to edit the accepted answer to add the part about the floating sidebar which is just a different way to get to the same setting.
    – fsb
    Commented Nov 6, 2019 at 15:22

You must log in to answer this question.

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