How can I save a CSV file using "|" as the delimiter instead of a comma "," on MacOS or Browser Based Excel

Question

I am using Microsoft® Excel for Mac Version 16.89 on MacOS Sonoma.

I need to export some CSV files where the delimiter is a vertical bar "|". While I can successfully import these files into Excel, I am unable to export them while retaining the "|" delimiter. Instead, Excel defaults to exporting as comma-separated, which causes issues with the data structure.

Performing a text replace after the fact is not a feasible solution because replacing commas would break the CSV file, as some of the cells already contain commas.

I’ve noticed that on Windows, there is a "List Separator" setting for the computer's settings (Control Panel > Clock and Region > Additional Settings...) that allows users to specify a custom delimiter like "|" and it carries over to be able to export in excel with such delimiter. Unfortunately, this feature does not appear to be available on Mac.

Could you please provide guidance on how to achieve this on Mac?

Answer 1

You may try formula to combine all the cells in to one column. Then paste value only and remove other columns.

=TEXTJOIN("|",FALSE,A1:D1)

When you open it as txt format, it will show as below. Then you can set list separator when you want to import to other software.

Answer 2

Thanks for your reply, I'll give this a try, but I don't think it's a sustainable solution since I have hundreds of CSV files that need to be edited. This approach requires manually selecting the new fields from each file, copying them into an external code editor, and then saving them in the required format. Given the volume, this process would be highly time-consuming and inefficient.

I will report back if the workaround works for my situation or not. Hopefully Excel can implement custom delimiter selection in the future as a permanent solution.

Answer 3

Are you sure the receiving system must have pipes as field delimiters?

When Excel generates a CSV it encloses the complete field in quotation marks if it contains a comma.

For example, if the Excel data is similar to this:

It appears this way when opened in a text editor:

The receiving program should not recognize the commas as delimiters if they are withing the quotation marks.