How to Convert an Excel File to a Text File using Comma Delimited (3 Methods)

To demonstrate the methods, we’ll use a dataset containing the Sales Amounts of some sellers.

Method 1. Using the File Tab

This method can convert a single sheet of an excel file to a text file. It cannot be used to convert multiple sheets within a workbook at the same time.

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

This method also only converts a single sheet of an excel file.

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.

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.

Things to Remember

You can convert only a single sheet within a workbook using Method 1 and/or Method 2.
To convert multiple sheets, use Method 3 to batch convert, or convert each sheet individually using Method 1 and/or Method 2.

Download Practice Book

Convert into Text File with Comma Delimited.xlsm