Convert Excel to Text File with Delimiter (2 Easy Approaches)

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.

convert excel to text file with delimiter: overview


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.

Convert Excel to Text File with a Tab Delimiter

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


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.

Convert Excel to Text File with a Custom (Pipe, Comma, etc.) Delimiter

  • 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

Masum Mahdy

Masum Mahdy

Hi there! I am Mahdy, a graduate of Naval Architecture from BUET, currently working as an Excel & VBA Content Developer in ExcelDemy. You are gonna find my published articles on MS Excel and other topics of my interest here in ExcelDemy's blog. You are most welcome to my profile!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo