Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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

Easily combine multiple worksheets/workbooks into single worksheet/workbook

It may be tedious to combine dozens of sheets from different workbooks into one sheet. But with Kutools for Excel’s Combine (worksheets and workbooks) utility, you can get it done with just several clicks! Full Feature Free Trial 60-day!

ad combine sheets books 1

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.


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.


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 - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Select the rang you will export to a text file (in our case, we select the Column A), and click Enterprise > 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 - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


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 - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Click Enterprise > Workbook > Split 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 - Includes more than 300 handy Excel tools. Full feature free trial 60-day, 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. Full Feature Free Trial 60-day!

ad split workbook pdf 1


In this Video, the Kutools tab and the Kutools Plus tab are added by Kutools for Excel. If need it, please click here to have a 60-day free trial without limitation!



Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    luizfeijo · 6 months ago
    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?
  • To post as a guest, your comment is unpublished.
    GILLOU · 7 months ago
    HELLO
    THANK YOU FOR YOUR VBA CODE
    SOMETIMES ON CERTAIN TEXT THERE IS "TEXT" IN EXPORT .TXT
    THANK YOU FOR WHY
  • To post as a guest, your comment is unpublished.
    Harish Kaundinya · 1 years ago
    Thanks for the awesome piece of VBA code to export data to a text file. I have used your code with some of my own. The data I am dealing with is extremely line-length specific and after the macro has run, the text file contains some double quotation marks " at random places, which was never present in my data before. I have tried adding a code line to remove this character at various stages but that does not seem to be working. I am aware that I can manually remove this but would like to resolve it in the VBA code.

    Sub Macro3()
    Dim wb As Workbook
    Dim saveFile As String
    Dim WorkRng As Range
    On Error Resume Next
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$B$10591").AutoFilter Field:=1, Criteria1:= _
    "=NSZAP*", Operator:=xlAnd
    Range("A1").Select
    ActiveCell.Offset(200, 0).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveCell.Rows("1:10101").EntireRow.Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select
    Selection.AutoFilter
    Range(Selection, Selection.End(xlDown)).Select
    Set WorkRng = Application.Selection
    Set wb = Application.Workbooks.Add
    WorkRng.Copy
    wb.Worksheets(1).Paste
    Selection.Replace What:="""", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    saveFile = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
    Selection.Replace What:="""", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    wb.SaveAs Filename:=saveFile, FileFormat:=xlText, CreateBackup:=False
    Selection.Replace What:="""", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    wb.Close
    Application.CutCopyMode = False
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End Sub
    • To post as a guest, your comment is unpublished.
      Tang Kelly · 1 years ago
      Hi Harish,
      For removing a specified character in bulk, you can replace the character with nothing.
      Ctrl + H to open the Find and Replace dialog box, type the specified character in the Find what box, and type noting in the replace with box.
  • To post as a guest, your comment is unpublished.
    Wild Bill · 1 years ago
    3/2
    You might also note that you don't need to specify the wb prefix once you've .Add ed the workbook since it becomes active. Specifying wb might or might not be more efficient but it can be omitted from some commands. Actually I entirely scrapped the variable wb; I just go Workbooks.Add, and use ActiveWorkbook when needed. (As you are suppressing ScreenUpdating it wouldn't be obvious to some that wb is Active. Tip for beginners (and higher): I always, always develop with ScreenUpdating and DisplayAlerts as True, and when done with development, I consider toggling them off for some passage of code.)
  • To post as a guest, your comment is unpublished.
    Wild Bill · 1 years ago
    2/2
    - vbYesNoCancel msgboxes and Booleans to indicate whether to export all, selection, or a user specified range
    - a static String for the prior range address
    - if len(that static)1 then I set WorkRng=activesheet.UsedRange (You can't copy multiple areas with a single copy, though with a little work you could walk the areas and copy them piecemeal.)
    - Idiotic Microsoft does not save off empty rows at the start and the bottom of the saved range, and does not save off empty columns at the leftmost and the rightmost of the saved range. When I detect that (UsedRange is not row 1, col. 1, or xlLastCell is not completely lower-right) I msgbox to ask user if they want to plug A1 or the lower right cell.
    - Then I decided to preemptively address the upper left issue by inserting a row and setting A1 to be text like "The following is for range "
    - I close with activeworkbook.close SaveChanges:=False
    - Long time habit since I'm an angry proponent of cleaning up (and the world is a sad place thanks to irresponsible programmers who shirk that responsibility when it matters), I set WorkRng = Nothing on the way out :)