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

The dataset below contains 4 columns. The first column displays the full names of individuals. The following three columns have divided these names into First Name, Middle Name, and Last Name.

Method 1 – Using the Control Panel to Convert an Excel File to a 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.

• Choose Time & Language. The Region option is available in this section.

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

• Choose Region.

• A dialogue box will pop up – choose Additional settings.

• A dialogue box will pop up. Enter the SHIFT+BACKLASH (shift+\) key in the List separator box. It will change the separator from comma (,) to pipe (|).

• Open the Excel file and go to File settings.

• Click Save as.

• A dialogue box will appear – select the file type as CSV(comma delimited) and Save the file.

• Right-click on the file Convert to pipe text, and Open with > Notepad.

• The file is converted to pipe text.

• Press CTRL+S to save the file.

Method 2 – Find and Replace to Convert an Excel File to a Text File with Pipe Delimiter

Steps:

• Convert the file to CSV(comma delimited). Have a look at Method 1.

• Open the file with Notepad.

• Click Edit and go to Replace.

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

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

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

We’ve attached a practice workbook where you may practice these methods.

Download the Practice Workbook