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

Table of Contents hide

Download Practice Workbook

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

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

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

Things to Remember

Practice Section

Conclusion

Download Practice Workbook

Convert to pipe text.xlsx

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.

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

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

From here, choose Region.

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.

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.

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.

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.

Now, open the file with Notepad.

After that, click Edit and go to Replace.

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

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.

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.