How to change CSV delimiter in Excel
For Windows 10 Users
Open Control Panel: Press the Windows key, type "Control Panel," and press Enter.
Change Date, Time, or Number Formats: Go to "Clock and Region" > "Region". A new window titled "Region" will open.
Access Additional Settings: In the "Formats" tab, click on "Additional settings...".
Edit the List Separator: In the "Numbers" tab of the window that opens, you'll see a field labeled "List separator". Here, you can change the comma to any other character you wish to use as your CSV delimiter.
Apply and OK: After making your change, click "Apply" and then "OK" to save your settings.
Restart Excel: If Excel was open during this process, restart it to ensure the changes take effect.
For Mac Users
Changing the CSV delimiter directly in Excel for Mac isn't straightforward because Excel uses the system's settings, much like Windows. However, Mac OS doesn't offer a simple way to change the delimiter as Windows does through the Control Panel. Instead, you can use a text editor to manually change the delimiter or use script-based solutions to convert files. For straightforward tasks, manually replacing commas with your preferred delimiter in a text editor might be the quickest solution.
Additional Considerations
Regional Settings: Changing the list separator affects all applications that use this setting, not just Excel. Keep this in mind if you decide to change it.
Data Integrity: Always ensure your data doesn't contain the character you're using as a delimiter. For example, if you choose a semicolon (;), make sure none of your data fields contain semicolons unless they are enclosed in quotes.
Alternative Methods
For more flexibility or to handle CSV files without altering system settings, consider using third-party data processing tools or programming languages like python, which offer libraries (e.g., pandas) specifically designed for managing CSV files with custom delimiters.