How to Use Pipe Delimited in Excel Files

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.

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.

Advertisement

Import Text File

Video of the Day

Step 1

Open Microsoft Excel.

Video of the Day

Step 2

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

Advertisement

Step 3

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

Step 4

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

Advertisement

Step 5

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.

Advertisement

Step 6

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.

Advertisement

Split Pipe Delimitated Columns

Step 1

Select the data that you want to split.

Advertisement

Step 2

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

Step 3

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

Advertisement

Step 4

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.

Advertisement

Step 5

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

Advertisement

Advertisement

references