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

Microsoft Excel has features to automatically convert Excel files to CSV files or text files. But what about converting 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


Download Practice Workbook


2 Ways to Convert Excel File to Text File with Pipe Delimiter

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

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


Similar Readings


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: How to Import Text File with Multiple Delimiters into Excel (3 Methods)


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.


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.


Related Articles

Mahbub
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo