Export an Excel spreadsheet as a flat file with vertical bar delimiters.

How to export an Excel spreadsheet as plain text using vertical bars as the delimiter (or other character of your choice)

Assume you have an Excel Spreadsheet containing columns and rows of data.

You want to convert this data into a text file using a delimiter – but Excel will only delimit with commas.

Here’s how to change the delimiter.

Close the spreadsheet before you proceed.

Open the Region settings – either:

-          Open control panel and chose Region

-          Tap the start button and type Region

You’ll see the Region dialog. Tap the Additional settings… button.

This will show the Customise Format dialog:

 Edit the List separator character to be what you want as the delimiter – i.e. the vertical bar | or tilde ~ character – my example is the vertical bar:

Tap OK and OK to save and close the changes.

Re-open your Excel spreadsheet. Here’s my sample:

 Tap File – Save a copy and change the format from Excel Workbook (*.xlsx) to CSV (Comma delimited)(*.csv) as shown here:

Optionally change the name and location for the file and tap Save.

Two things will happen.

-          Excel will show the unformatted data and warn about possible data loss.

-          A new file will be created.

You can close Excel now if you want.

Explore to the file and open it with a text editor such as Notepad. You will see that is has been created using the list separator you defined in the Region settings:

You may wish to revert the list separator value back to commas – repeat the steps above but set List Separator to the comma character.