How to Convert Excel to Text File with Pipe Delimiter (2 Ways)

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel has features to automatically convert Excel files to CSV files or text files. Moreover, we can convert Excel files to a pipe-delimited text file. In this blog post, we will see two simple methods to convert Excel to text file with pipe delimiter. We will use a sample dataset for your better understanding.

convert excel to text file with pipe delimiter


How to Convert Excel File to Text File with Pipe Delimiter: 2 Creative Ways

Here, we will see the use of the Control Panel and Find and Replace method to convert an Excel file to a pipe-delimited text file.


Method 1: Using Control Panel to Convert Excel File to Pipe Delimited Text File

We have to go to the Region setting from the control panel for this method.

Steps:

  • Go to the computer Settings.

convert excel to text file with pipe delimiter Using Control Panel Region

  • Now, choose Time & Language. As you can see, the Region option is available in this section.

convert excel to text file with pipe delimiter Using Control Panel Region

  • After that, Choose the Date, time, & regional formatting or Region.

  • From here, choose Region.

convert excel to text file with pipe delimiter by list separator

  • As a result, a dialogue box will pop up and choose Additional settings.

  • Again, a dialogue box will pop up. Now, we will type the SHIFT+BACKLASH (shift+\) key in the List separator box. It will change the separator from comma (,) to pipe (|).

  • At this point, open the Excel file and go to File settings.

convert excel to text file with pipe delimiter by list separator

  • Now, click Save as.

  • As a consequence, a dialogue box will appear and select the file type as CSV(comma delimited) and Save the file.

convert excel to text file with pipe delimiter by list separator

  • After this, right-click on the file Convert to pipe text then Open with > Notepad.

  • Finally, we will see, that the file is converted to pipe text.

  • Lastly, press CTRL+S to save the file.

Method 2: Find and Replace to Convert Excel File to Text File with Pipe Delimiter

Another easy method, we can use is, using the find and replace option in the notepad.

Steps:

  • First, convert the file to CSV(comma delimited). If you cannot recall how to convert the file to CSV, please have a look at Method 1.

convert excel to text file with pipe delimiter

  • Now, open the file with Notepad.

convert excel to text file with pipe delimiter by find and replace

  • After that, click Edit and go to Replace.

  • Here, replace Comma (,) with Pipe (|) and click Replace all.

convert excel to text file with pipe delimiter by find and replace

  • As a result, all the commas will be converted to the pipe as we want and now, save the file by pressing CTRL+S.

That’s it.

Read More: Convert Excel to Text File with Delimiter


Things to Remember

  • You can convert one Excel sheet at a time, so for multiple sheets to convert, repeat the process of selecting the sheet.
  • Due to an extra line at the bottom of the file, several people have been unable to load their flat files. Click on the first blank line at the end of the file and backspace until your cursor is at the end of the last line containing data when you open the file.

Practice Section

The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, we’ve attached a practice workbook where you may practice these methods.


Download Practice Workbook


Conclusion

That’s all for the article. These are 2 different methods to convert Excel to text file with pipe delimiter. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mahbubur Rahman
Mahbubur Rahman

Mahbubur Rahman is a highly skilled and experienced professional with a strong background in both engineering and business administration. With an impressive technical skill set and a passion for innovation and problem-solving, Mahbubur has achieved great success in his field.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo