Export Excel to txt (Ultimate Guide)

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.

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.

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.

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

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.

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

After that, click on the Save button.

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.

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

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

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 ($).

1

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.

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.

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.

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.

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

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

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

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.

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.

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

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.

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.

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.

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

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.

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.

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

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.

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

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

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

• Convert Excel to Text File with Delimiter
• Convert Excel to Text File with Pipe Delimiter

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