To demonstrate the methods, we’ll use a dataset containing the Sales Amounts of some sellers.
Method 1 – Using the File Tab
STEPS:
- Click the File tab in the ribbon.
- Select Save As.
- In the Save as Type field, select CSV (Comma delimited) (*.csv).
- Click Save.
- A Message Box pops up. Click OK to proceed.
- To view the results, navigate to the directory where the text file was saved.
- Open the file with a text editor app.
- The results should look like this:
Read More: How to Create CSV File from Excel
Method 2 – Retaining Special Characters Using the File Tab
Some special characters have been added to our dataset to demonstrate the method.
STEPS:
- Click the File tab in the ribbon.
- Select Save As.
- In the Save as Type field, select CSV UTF-8 (Comma delimited) (*.csv).
- Click Save.
- A warning message pops up. Click OK.
- To view the results, navigate to the directory where the converted file was saved.
- Open the file with a Text Editor app.
- The results will look like this.
Read More: Convert Excel to Comma Delimited CSV File
Method 3 – Using VBA to Batch Convert Multiple Sheets
STEPS:
- Click the Developer tab and select Visual Basic.
- Click the Insert tab and select Module.
- The Module window opens. Enter the following code:
Sub Convert_Multiple_Excel_File_to_Text()
Dim xWks As Worksheet
Dim xPath As String
Application.ScreenUpdating = False
xPath = ActiveWorkbook.path & "\" & Left(ActiveWorkbook.Name, _
InStr(ActiveWorkbook.Name, ".") - 1)
For Each xWks In Worksheets
xWks.Copy
ActiveWorkbook.SaveAs Filename:=xPath & "_" & xWks.Name & ".csv", _
FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close False
Next
Application.ScreenUpdating = True
End Sub
- Press Ctrl + S on the keyboard to save the code.
- Press F5 on the keyboard to run the code.
- Multiple converted files will appear in the directory where the excel file is saved.
- To view the results, open these files with a Text Editor app.
- The results will look like the picture below. Some information, like the special characters, may be lost.
Read More: How to Make a CSV File in Excel for Contacts
Things to Remember
- You can convert only a single sheet within a workbook using Method 1 or Method 2.
- To convert multiple sheets, use Method 3 to batch convert or convert each sheet individually using Method 1 or Method 2.
Download the Practice Book
Related Articles
- How to Convert Excel to CSV without Opening
- How to Convert Multiple Excel Files to CSV
- How to Save Excel File as CSV with Commas
- Set Comma Instead of Semicolon as Delimiter in Excel CSV
- Save Excel as CSV with Double Quotes
- [Fixed!] Excel Not Saving CSV with Commas
- How to Convert Excel Files to CSV Automatically
<< Go Back to Export Excel to CSV | Export Data from Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!