Convert 2013 Excel Document to Pipe-Delimited Format

[beadedbytes]

Is there a way to save a 2013 Excel document in a pipe-delimited format?

I'm aware that some metrics under the Control Panel can be manipulated (specifically Region and Language setting), but was hoping there might be a more straightforward method and one that does not require a macro.

If a macro is the best solution, I would appreciate a solution.

Thank you in advance.

 

[zelgar]

Without a macro, you can use the CONCATENATE command to combine data into one line. For example, if you had the following data in Row 2, AAA, BBB, CCC, DDD, =CONCATENATE(A2,"|",B2,"|",C2,"|",D2) would result in AAA|BBB|CCC|DDD

To make your file,
1. Insert the CONCATENATE command
2. Copy & Paste the CONCATENATE command
3. Copy all of the cells with the CONCATENATE command, and Paste Special Values Only (this will get rid of the CONCATENATE command, but will allow you to delete the other rows of data
4. Delete the data from the source cells of the CONCATENATE command (e.g., Cols A, B C and D in my example above)
5. Save As a text file

 

[mintjulep]

You could copy into a Word document - this would result in a table.

Word then has a feature to convert tables to text allowing you to specify the delimiter between cells. Specify a pipe.

 

[combo]

You can also save as csv or tab delimited text file, open in notepad and do search&replace (tab can be copied).

combo

 

[SkipVought]

All kinds of ways to "skin a cat"!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[xlhelp]

You can also go to Regional settings in Control Panel. Under the Format tab click on "Customize this Format..." or "Advanced Format"....or whatever it is for your OS and change the list separator to a pipe symbol. Close.

Open Excel file and save it as a CSV file. It will be saved as a pipe delimited file.

Don't forget to change back the list separator.


Never miss an opportunity to shut up