How to export Excel data (selection or sheets) to Text files in Excel?
If you are required to deliver a workbook in the format of text file, you need to convert the workbook into text files. And this article will introduce you two methods to export Excel data into text files easily.
- Export one single sheet to text file in Excel
- Batch export multiple sheets to separate text files in Excel
- Export selection (or one column) to text file with VBA
- Export selection (or one column) to text file with Kutools for Excel
By default, the Excel data will be saved as workbooks in the format of .xlsx. However, we can export a worksheet of existing workbook as text files with the Save As feature. Please do as follows:
1. Shift to the worksheet which you will export to text file, and click File (or Office button) > Save As.
2. In the opening Save As dialog box, select the destination folder you will save the exported text file into, name the file in the File name box, and then select the Unicode Text (*.txt) from the Save as type drop down list. See screenshot:
3. Then it will pop up two warning dialog boxes asking you to export only active worksheet as text file and leave out any incompatible features with text file. Click OK button and Yes button successively.
Then the data in the active worksheet are exported as a new text file.
Note: the Save As command is only able to export data of active worksheet as a text file. If you want to export all data of the whole workbook, you need to save each worksheet as a text file individually.
The following VBA code also can help you to export the selected range data (for example one column) to the text file, please do as this:
1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA: export selection or entire worksheet to text file
Sub ExportRangetoFile() 'Update 20130913 Dim wb As Workbook Dim saveFile As String Dim WorkRng As Range On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) Application.ScreenUpdating = False Application.DisplayAlerts = False Set wb = Application.Workbooks.Add WorkRng.Copy wb.Worksheets(1).Paste saveFile = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt") wb.SaveAs Filename:=saveFile, FileFormat:=xlText, CreateBackup:=False wb.Close Application.CutCopyMode = False Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
3. Then press F5 key to run this code. And then select the data range that you want to export in the popping up dialog box, and click the OK button, see screenshot:
4. And now in another Save As dialog box, please specify a name for this text file and a folder to put this file into, and click the Save button.
The Save As command can export all data in the active worksheet as text. What if exporting all data of specified inactive worksheets, or part of the data in a worksheet as a text file? Kutools for Excel's Export Range to File utility can help us export and save the data in selection as a text file easily..
1. Select the rang you will export to a text file (in our case, we select the Column A), and click Kutools Plus > Import / Export > Export Range to File, see screenshot:
2. In the Export Range to File dialog box, do as below screenshot shown:
(1) Check the Unicode Text option in the File format section;
(2) Check the Save the actual values option or Save values as shown on-screen option as you need in the Text options section;
(3) Specify the destination folder you will save the exported text file into;
(4) CLick the Ok button.
3. Name the exported text file in the new opening dialog box, and click the Ok button.
And then the selection (selected Column A) has been exported as a text file and saved into the specified folder.
Sometimes you may need to export multiple worksheets to multiple text files in Excel. The Save As feature may be a little tedious! Do not worry! Kutools for Excel’s Split Workbook utility provide Excel users an easy workaround to export multiple worksheets to many separate text files with several clicks.1. Click Kutools Plus > Workbook > Split Workbook. See screenshot:
2. In the opening Split Workbook dialog box, please do as follows:
(1) Check the worksheets you will export to separate text files in the Workbook name section;
(2) Check the Specify save format option, and then select the Unicode Text (*.txt) from below drop down list, see left screenshot:
(3) Click the Split button.
3. And then in the popping up Browse For Folder dialog box, select the destination folder you will save exported text files into, and click the OK button.
So far each specified worksheet has been exported as a separate text file and saved into the specified folder.