Export Excel to txt (Ultimate Guide)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will discuss how to export Excel to txt (or .txt) files. We will present the process to export a single sheet (active sheet) or multiple sheets and a specific range to a txt file. We will also discuss how to import any txt file into Excel.

Exporting Excel to txt files is often required while sharing data. We also require it for data transformation, importing information to a database, data backup, archiving data, and integrating with other applications such as programming software, analysis, statistical or report-making tools, etc.

Overview of how to Export Excel to txt


Download Practice Workbook

You may download the following file and practice yourself.


3 Ideal Methods to Export Excel to txt

We can export a single sheet (the active sheet), multiple sheets, or a specific range to txt files. A dataset similar to the following is present in each sheet of the sample workbook.

Sample Dataset


1. Export Active Sheet in Excel to txt File

When we save a workbook as a txt (or .txt) file, only the active sheet is exported. We have two different txt file options available: Text (Tab delimited) and Unicode Text.


1.1 Export to Tab Delimited Text File

To export the active sheet to tab delimited txt file in Excel, first, go to the File tab.

Going to the File Options

Afterward, click on the Save As option and double-click on the This PC or any other memory option.

Selecting Memory Option for Saving txt File

At this point, the Save As dialog box will appear. Go to the directory where you want to save your file >> input a File name (the default File name is the same as the Workbook name) >> click on the dropdown icon of the Save as type option.

Selecting Required Directory

Select the Text (Tab delimited) option from the dropdown list.

Selecting Text (Tab delimited) Option for Save as Type

After that, click on the Save button.

Saving Excel Workbook as Tab Delimited txt File

At this time, a warning message will appear as txt files can contain only one sheet and our workbook contains multiple sheets. To save only the active sheet, click the OK button.

Confirming Export Active Excel Sheet to txt

Now, go to the directory where you saved the file to view the txt file. Double-click on the file to open it.

txt File in Specified Directory

As you can see, the dataset in the active sheet has been exported to the txt file.

Output After Excel Active Sheet Exported to txt


1.2 Export to Unicode Text File

The tab delimited txt files encode data in UTF-8 format. The UTF-8 encoding format has limited support for special characters and complex formatting within cells. For example, consider the following dataset where the prices are mentioned in Indian Rupees () instead of US Dollars ($).

1Dataset with Price in Indian Rupees

After exporting the sheet that contains this dataset to a Text (Tab delimited) file, the output looks like the following. As you can see, the Rupee () symbols are not exported properly.

Output After Exporting Excel Sheet with Rupee Symbol to Tab Delimited txt

A remedy for this solution is to set the Save as type to Unicode Text while saving the workbook using the same steps as the previous method.

Saving Excel Workbook as Unicode Text File

As Unicode Text files use UTF-16 encoding, it supports a wide range of characters. Hence, the data from the active sheet is exported with proper formatting as shown below.

Output After Exporting Excel Sheet with Rupee Symbol to Unicode Text


2. Convert Multiple Sheets to Separate txt Files

Since only the active sheet is exported when a workbook is saved as a txt (or .txt) file, we require going to each sheet and saving the workbook as a txt file. For workbooks with a large number of sheets, this can be tedious. A simple remedy to this problem is to execute VBA macros.

Here are two ideal examples to convert multiple sheets to separate txt files.


2.1 Convert All Sheets to Separate txt Files

To convert all sheets to separate txt files, first, open the Visual Basic Editor by going to the Developer tab and clicking on the Visual Basic option. An alternative method to open the Visual Basic Editor window is to use the keyboard shortcut Alt + F11.

Opening Visual Basic Editor from Developer Tab

Afterward, click on the Insert tab and select the Module option.

Inserting Module

Then, enter the following code in the module and change the folder path to your required directory. Next, Save and Run the VBA code.

Executing VBA Code to Export All Active Sheet to txt in Excel

Sub ExportAllSheetsToTxt()
    
    Set wb = ThisWorkbook
    Dim ws_count As Integer
    Dim i, j, k As Integer
    Dim folder_path As String
    Dim file_num As Integer
    
    ws_count = wb.Sheets.Count
    folder_path = "E:\Exceldemy\New Text Files\"
    
    For i = 1 To ws_count
        file_num = FreeFile
        Set ws = wb.Sheets(i)
        Open folder_path & ws.Name & ".txt" For Output As #file_num
        Set used_rng = ws.UsedRange
        For j = 1 To used_rng.Rows.Count
            For k = 1 To used_rng.Columns.Count
                Print #file_num, used_rng.Cells(j, k).Text;
                Print #file_num, vbTab;
            Next k
            Print #file_num, vbNewLine;
        Next j
        Close #file_num
    Next i
    
End Sub 
			

After executing the VBA Code, open the specified directory. As the sample workbook contains 5 sheets, you will get 5 separate txt files (file names are the same as the worksheet names).

All Excel Sheets Exported to txt


2.2 Convert Specific Sheets to Separate txt Files

Sometimes, instead of converting all sheets in a workbook, you may require converting specific sheets. In such instances, we can apply the following VBA code by using the same steps as the previous example.

Executing VBA Code to Export Specific Sheets to txt in Excel

Sub ExportSpecificSheetsToTxt()
    
    Set wb = ThisWorkbook
    Dim ws_count As Integer
    Dim i, j, k As Integer
    Dim folder_path As String
    Dim file_num As Integer
    Dim ws_indexArr() As String
    Dim user_input As String
    
    user_input = InputBox("Enter the index of the Sheets (separated by commas) to be converted to txt files:")
    
    ws_indexArr = Split(user_input, ",")
    ws_count = UBound(ws_indexArr) + 1
    
    If ws_count > wb.Sheets.Count Then
        MsgBox "Enter valid sheet indices."
        Exit Sub
    End If
    
    For i = LBound(ws_indexArr) To UBound(ws_indexArr)
        If ws_indexArr(i) > wb.Sheets.Count Then
            MsgBox "Enter valid sheet indices"
            Exit Sub
        End If
    Next i
    
    folder_path = "E:\Exceldemy\New Text Files\"
    
    For i = LBound(ws_indexArr) To UBound(ws_indexArr)
        file_num = FreeFile
        Set ws = wb.Sheets(Int(ws_indexArr(i)))
        Open folder_path & ws.Name & ".txt" For Output As #file_num
        Set used_rng = ws.UsedRange
        For j = 1 To used_rng.Rows.Count
            For k = 1 To used_rng.Columns.Count
                Print #file_num, used_rng.Cells(j, k).Text;
                Print #file_num, vbTab;
            Next k
            Print #file_num, vbNewLine;
        Next j
        Close #file_num
    Next i
    
End Sub

After executing the VBA code, an InputBox like the following will appear. Input the index of the sheets (separated by commas)  you want to convert to separate txt files. Finally, click the OK button.

Inserting the Indices of the Excel Sheets to Export them to txt

As I provided the indices of 3 sheets (1,2,4), there will be 3 txt files in the specified directory.

Specified Sheets Exported to Txt


3. Export Selected Range in Excel to txt File

Instead of the entire sheet, we can also export any specific range in Excel to txt files. Insert the following VBA code in a module and then Save and Run it.

Executing VBA Code to Export Specific Range to Txt in Excel

Sub ExportSpecificRangetoTxt()
    
    Set ws = ThisWorkbook.ActiveSheet
    Dim input_rng As String
    
    input_rng = InputBox("Reference of the required range: (Example:- A1:C10)")
    Set working_rng = ws.Range(input_rng)
    
    Dim i, j As Integer
    Dim folder_path As String
    Dim file_name As String
    Dim file_num As Integer
    
    file_name = "Specific Range-" & ws.Name
    folder_path = "E:\Exceldemy\New Text Files\"
    file_num = FreeFile
    
    Open folder_path & file_name & ".txt" For Output As #file_num
    
    For i = 1 To working_rng.Rows.Count
        For j = 1 To working_rng.Columns.Count
            Print #file_num, working_rng.Cells(i, j).Text;
            Print #file_num, vbTab;
        Next j
        
        Print #file_num, vbNewLine;
    Next i
    
    Close #file_num
End Sub

			

After executing the VBA code, an InputBox like the following will appear. Insert the reference of the range you want to export to a txt file and click the OK button.

Inserting Reference of the Range to Export it to Txt

After opening the specified directory in the VBA code, you will find a txt file like the following. Double-click on the file to open it.

Specified Range Exported to txt

As I provided the reference of the range B4:D10, the txt file will contain only the content of these cells.

Specified Range in txt File


How to Import a txt File into Excel

We often require importing data from a txt (or .txt) file into Excel. We can use Excel’s built-in features for this purpose. For example, let’s import the following data into an Excel file from a Text (Tab delimited) file.

txt File to Import Data into Excel

First, select Cell B4 (i.e. the cell where you want to put the data from the text file) >> go to the Data tab >> click on the From Text/CSV option.

Selecting From CSV or TXT option from Data Tab

Afterward, go to the directory where the required txt file is located >> select the txt file >> click on the Import button.

Selecting the txt File

At this point, the Import Text File into Excel.txt will pop up. A preview of the imported data is available here. If any modifications are required, then click on the Transform Data button. Else, click on the dropdown icon of the Load option and select the Load To option.

Loading Imported Data

Note: If you select the Load option, the data from the txt file will be imported to a new worksheet directly.

>> After selecting Load To, the Import Data dialog box will appear.

>> For the Select how you want to view this data in your workbook option, the Table option will be selected by default. You can change it to other available options if required.

>> For the Where do you want to put the data? option, select the radio button of the Existing worksheet option. As we selected Cell B4 while importing the txt file, the default location is set to Cell B4. You can modify this reference if required. You can also keep the default New Worksheet option.

>> Finally, click on the OK button.

Selecting Location for Importing Data

The imported data is formatted as the following in the selected location.

Imported Data in Excel


Things to Consider

  • When we save a workbook as a txt (or .txt) file, only the active sheet is exported.
  • The Text (Tab delimited) .txt files are UTF-8 encoded. Therefore, they don’t support many special characters.
  • The Unicode Text .txt files are UTF-16 encoded. Hence, they support a wide range of special characters and languages.
  • While exporting Excel to txt, various Excel-specific features like formulas, formatting, and charts may not be preserved.

Frequently Asked Questions

  • What is the difference between CSV and txt formats when exporting from Excel?

Answer: In CSV (Comma Separated Values) files, each value is separated by a comma. On the other hand, we can specify delimiters such as Tabs, Spaces, Pipes, etc. CSV files are commonly used for structured data, while txt files provide more flexibility in terms of delimiters.

  • Can I export Excel formulas to a text file?

Answer: No, when exporting to a text file, only the cell values are exported. Formulas, formatting, and other Excel-specific features like charts or graphs are not included in the exported text file.

  • How can I preserve the formatting of an Excel file when exporting it to a txt file?

Answer: Excel file’s formatting can not be exported to a txt file. If you need to preserve the formatting, you can try to export the Excel to PDF instead of txt.


Conclusion

This concludes our article on how to export Excel to a txt file. We discussed the process to export a single sheet, multiple sheets, and a specific range. We hope that the demonstrated methods were sufficient for your requirements.  Let us know your feedback on the article. Visit our website ExcelDemy.com for more articles related to Excel.


Export Excel to Txt: Knowledge Hub

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Seemanto Saha
Seemanto Saha

Seemanto Saha graduated in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. He has been with ExcelDemy for a year, where he wrote 40+ articles and reviewed 50+ articles. He has also worked on the ExcelDemy Forum and solved 50+ user problems. Currently, he is working as a team leader for ExcelDemy. His role is to guide his team to write reader-friendly content. His interests are Advanced Excel, Data Analysis, Charts & Dashboards, Power Query,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo