I want to import a CSV file to Excel. The CSV file has comma separated data (go figure), but the delimiter in the Text Import Wizard is set to "Tab" by default. How can I change the default to "Comma" instead?
Excel – How to change default delimiter in the Text Import in Excel
csvimportmicrosoft excel
Related Solutions
I don't think there's any way of changing the default delimiter character (i.e. Tab) used while copying text to the clipboard from Excel. What you can do however is create a macro to achieve the result you want.
Create a macro named something like CopySelectedCells and optionally assign a keyboard shortcut so you can invoke it quickly (I assigned it Ctrl+Shift+C for example):
From the main Macro dialog shown above, click the Edit button to open the VBA Editor.
Go to
Tools menu / References
and click on the Browse button.Add Windows\System32\FM20.dll:
Select the Microsoft Forms 2.0 Object Library option now added to the Available References list:
Edit the macro code to look like the following:
Sub CopySelectedCells() Dim str As String For Each rangeRow In Selection.Rows For Each rangeCol In rangeRow.Cells str = str & rangeCol.Value & "," Next str = Left(str, Len(str) - 1) & vbCrLf Next With New DataObject .SetText str .PutInClipboard End With End Sub
Close the VBA Editor, select a range of cells and invoke the macro, then paste in Notepad to see the comma-separated result.
While opening CSV files, Excel will use a system regional setting called List separator
to determine which default delimiter to use.
Microsoft Excel will open .csv files, but depending on the system's regional settings, it may expect a semicolon as a separator instead of a comma, since in some languages the comma is used as the decimal separator. (from Wikipedia)
On Windows, you can change the List separator
setting in the Regional and Language Options
as specified on the Office support website :
Change the separator in a CSV text file
- Click the Windows Start menu.
- Click Control Panel.
- Open the Regional and Language Options dialog box.
- Click the Regional Options Tab.
- Click Customize / Additional settings (Win10).
- Type a new separator in the List separator box.
- Click OK twice.
Note: this only works if the Decimal symbol is not also designated as comma (in line with the Wikipedia citation above). If it is, Excel will not use comma as the List separator, even if chosen. For many non-United States regions, comma is the default Decimal symbol.
On Mac OS X, this setting seems to be deduced from the decimal separator setting (in the Language & Region pane of System Preferences, go to Advanced). If the Decimal Separator is a point then the default CSV separator will be a comma, but if the Decimal Separator is a comma, then the default CSV separator will be a semicolon.
As you said yourself in the comment, there is an alternative for Mac users to quickly look at those CSV files. It's plugin for Quick Look called quicklook-csv that handles separator detection.
Best Answer
This is a Windows setting that will be used by all programs that refer to it (Excel in this case).