Masum Mahdy
Text files consume less memory and are faster than Excel spreadsheets. Delimiters make the text files more readable, especially those which have numeric data in them. In this article, we will discuss 2 techniques to convert an Excel file to Text (txt) format with a delimiter with proper illustrations.
To get a quick idea of what we are going to get, please look at the following image.
Download Practice Workbook
Click the following button to download the practice workbook for free.
2 Ways to Convert Excel Spreadsheet to Text File with Delimiter
Let’s get introduced to the sample dataset first. The following dataset contains sales data of a Super Shop “X”.
Now, we will convert this into .txt format with a delimiter using the following techniques.
1. Convert Excel to Text File with a Tab Delimiter
Converting an Excel file to a text file (TXT format) is very easy indeed. Besides, you don’t have to go extra steps to have tab delimiters in such converted text files as tab delimiters are built-in in such text files. Just execute the following simple steps.
Steps:
- Open your Excel file and go to the File tab.
- Then click on the Save as option.
- From the following window, click on File type and select Text (Tab delimited) (*.txt) file type.
- Choose a suitable location, and then press the Save button.
Now, go to the folder where you’ve saved the text file and open it. It will be like this. 👇
Read More: VBA Code to Convert Text File to Excel (7 Methods)
Similar Readings
- Import Data from Excel into Word Automatically Using VBA (2 Ways)
- How to Extract Data from Cell in Excel (5 Methods)
- How to Extract Data from Excel to Word (4 Ways)
- Extract Data from One Sheet to Another Using VBA in Excel (3 Methods)
- How to Pull Data from Multiple Worksheets in Excel VBA
2. Convert Excel to Text File with a Custom (Pipe, Comma, etc.) Delimiter
If you don’t want to use the tab character as a delimiter in your converted text file, no worries! Here we will show you how to change the built-in separator in Windows settings (I am assuming that you are a Windows user). The steps are as simple as the ones below.
Steps:
- Go to the Control Panel of your Windows. You can get to it by pressing Windows + R, then typing control panel in the search bar, and pressing OK.
- Now, choose Large icons or Small icons from the View by drop-down button. All the features in the Control Panel will be visible. Click on the Region menu.
- The Region window will appear. Now, click on the Additional settings button.
- The Customize Format window will appear this time. Carefully look for the List separator and change the default separator to a Pipe ‘|’ delimiter.
- You can type the Pipe delimiter by pressing the SHIFT+Backslash keys simultaneously. The Backslash key is situated just above the ENTER key.
- Now press Apply, and then OK buttons.
- Press Apply and OK again consecutively.
- Now, go to the File tab >> Click on Save as option and select the file type as CSV UTF-8 (Comma delimited) (*.csv). Then press the Save button.
- We are almost done. Now, go to the folder where the file is saved and right-click on the file name. Choose Notepad or any other suitable application you have.
Look at the following image. The text file has the data delimited with pipe characters.
Read More: How to Convert Excel to Text File with Pipe Delimiter (2 Ways)
Conclusion
This brief article has discussed 2 techniques with which you can convert your Excel files to TXT format with pipe, comma, or tab delimiters. If you have any confusion or queries, please ask us in the comment box. Visit our site ExcelDemy to read more Excel-related articles.
Related Articles
- Extract Filtered Data in Excel to Another Sheet (4 Methods)
- How to Extract Data from Excel Sheet (6 Effective Methods)
- How to Extract Data from Image into Excel (With Quick Steps)
- Transfer Data from One Excel Worksheet to Another Automatically with VLOOKUP
- How to Convert Notepad to Excel with Columns (5 Methods)
- Excel VBA: Pull Data Automatically from a Website (2 Methods)
