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.
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.
- 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 (|).
- 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.
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: 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.
