How to Use Pipe Delimited in Excel Files

You can import a pipe delimited file into Microsoft Excel to transfer easily the data from another system, such as a database, web application or software program. The pipe character separates each field of text. Most delimited files are separated by either a comma or a tab; however, the pipe can also be used. Delimited files can be read and shared across many different platforms, making it an ideal data sharing format. You can import up to 1,048,576 rows and 16,384 columns.

Import Text File

Step

Open Microsoft Excel.

Step

Click “From Text” in the “Get External Data” section on the “Data” tab. Navigate to the location of the saved data file, and click “Open.” The data file needs to be saved as a TXT file for this process to work. This opens the “Text Import Wizard.” You can also choose to connect to get external data “From Access,” “From Web” or “From Other Sources.”

Step

Select “Delimited:” from the “Original Data Type” section. Click “Next.”

Step

Select “Other” and enter a “|” in the “Delimiters” section. Verify that the data looks corrects in the “Data Preview” section and click “Next.”

Step

Choose a column format for the first column. The options are “General, “Text” and “Date.” Choose “General” to convert numeric values to numbers, “Date” to convert values to dates, and the remaining values to “Text.” You can also choose “Do Not Import Column (skip)” to not import the column.

Step

Click the next column in the “Data Preview” section and repeat Step 5 until all the columns have been formatted or skipped. Click “Finish” to open the text file with pipe-delimited text separated into columns.

Split Pipe Delimitated Columns

Step

Select the data that you want to split.

Step

Click “Text to Columns” in the “Data Tools” group on the “Data” tab. This opens the “Convert Text to Columns Wizard” dialog box.

Step

Select “Other” and enter a “|” in the “Delimiters” section. Verify that the data looks corrects in the “Data Preview” section and click “Next.”

Step

Choose a column format for the first column. The options are “General, “Text” and “Date.” Choose “General” to convert numeric values to numbers, “Date” to convert values to dates, and the remaining values to “Text.” You can also choose “Do Not Import Column (skip)” to not import the column.

Step

Click the next column in the “Data Preview” section and repeat Step 4 until all the columns have been formatted or skipped. Enter a cell value in the “Destination” column to place the split data or leave “Destination” blank to overwrite the existing data. Click "Finish."