Excel open pipe delimited file automatically

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.

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.

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

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

Similar Readings

• How to Extract Data from a List Using Excel Formula (5 Methods)
• Excel Macro: Extract Data from Multiple Excel Files (4 Methods)
• How to Extract Data from Excel to Word (4 Ways)
• How to Pull Data from Multiple Worksheets in Excel VBA
• Extract Data from One Sheet to Another Using VBA in Excel (3 Methods)

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.

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

• VBA Code to Convert Text File to Excel (7 Methods)
• How to Import Data into Excel from Another Excel File (2 Ways)
• How to Import Data from Secure Website to Excel (With Quick Steps)
• Transfer Data from One Excel Worksheet to Another Automatically with VLOOKUP
• How to Import Data into Excel from Web (with Quick Steps)
• How to Extract Data from Image into Excel (With Quick Steps)