Skip to main content

How to export Excel data (selection or sheets) to Text files in Excel?

Author: Kelly Last Modified: 2020-05-08

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

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:
export to text file with Save As feature

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.


Export selection (or one column) to text file with VBA

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:
Select one column to export as text file

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.


Export selection (or one column) to text file with Kutools for Excel

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

Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download now!

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:
Excel addin:export selection to text file

2. In the Export Range to File dialog box, do as below screenshot shown:
Excel addin: export selection to text file
(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.
name the exported text file

And then the selection (selected Column A) has been exported as a text file and saved into the specified folder.

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now


Export multiple sheets to separate text files in Excel

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. 

Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download now!

1. Click Kutools Plus > WorkbookSplit Workbook. See screenshot:
Excel addin: export multiple sheets to text files

2. In the opening Split Workbook dialog box, please do as follows:
Excel addin: export multiple sheets to text files
(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.

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now

Easily mass save each sheet as a separate PDF/text/csv file or workbook in Excel

Normally we can save active worksheet as a separate .pdf file, .txt file, or .csv file with the Save As feature. But Kutools for Excel’s Split Workbook utility can help you easily save each workbook as separate PDF/TEXT/CSV file or workbook in Excel.


ad split workbook pdf 1

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now


Demo: export Excel data (selection or sheets) to Text files in Excel


Kutools for Excel: Over 300 handy tools at your fingertips! Start your 30-day free trial with no feature limitations today. Download Now!

Related Articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (19)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
How would I amend the code for Export selection (or one column) to text file with VBA to autofill the filename with the contents of cell B2?
thanks
This comment was minimized by the moderator on the site
Hi,This is great, thanks a lot.
How would I alter the code for Export selection (or one column) to text file with VBA to auto fill the filename with the contents of cell B2?
This comment was minimized by the moderator on the site
Hi, this code is very useful for me. So thank you very much.
I want to ask that: When i'm using this code, a new line append automatically at the end of the txt content.
Can you help me about preventing this by vba?
This comment was minimized by the moderator on the site
hi guys, i used that code:
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

and have working, but i need to savea column with cells that contain the function"concatenate", and when i play the vba code, and i save the .TXT file, only what appears it's #REF. how can i to fix it? cause i need the data that appears on that cells?
This comment was minimized by the moderator on the site
Has anyone been able to figure this out? I am having the same issue.
This comment was minimized by the moderator on the site
HELLO
THANK YOU FOR YOUR VBA CODE
SOMETIMES ON CERTAIN TEXT THERE IS "TEXT" IN EXPORT .TXT
THANK YOU FOR WHY
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations