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

STEPS:

  • Click the File tab in the ribbon.

Convert Excel File to Text File with Comma Delimited Using the File Tab

  • Select Save As.

Convert Excel File to Text File with Comma Delimited Using the File Tab

  • In the Save as Type field, select CSV (Comma delimited) (*.csv).

Convert Excel File to Text File with Comma Delimited Using the File Tab

  • Click Save.

Convert Excel File to Text File with Comma Delimited Using the File Tab

  • A Message Box pops up. Click OK to proceed.

Convert Excel File to Text File with Comma Delimited Using the File Tab

  • 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:

Convert Excel File to Text File with Comma Delimited Using the File Tab

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.

Transform Excel File to Text File Keeping Special Characters with Comma Delimited

STEPS:

  • Click the File tab in the ribbon.

Transform Excel File to Text File Keeping Special Characters with Comma Delimited

  • Select Save As.

Transform Excel File to Text File Keeping Special Characters with Comma Delimited

  • In the Save as Type field, select CSV UTF-8 (Comma delimited) (*.csv).

Transform Excel File to Text File Keeping Special Characters with Comma Delimited

  • Click Save.

Transform Excel File to Text File Keeping Special Characters with Comma Delimited

  • A warning message pops up. Click OK.

Transform Excel File to Text File Keeping Special Characters with Comma Delimited

  • 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.

Transform Excel File to Text File Keeping Special Characters with Comma Delimited

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

<< Go Back to Export Excel to CSV | Export Data from Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo