How to Convert Microsoft Excel Spreadsheets to Text Files

How to Convert Microsoft Excel Spreadsheets to Text Files
Page content

Saving Excel Files as Text

The easiest way to convert MS-Excel table to a text file is by clicking on “Save As,” inputting the file name and going down to the “save as type option” to select any text-based option.

The various options are:

  • Text (tab delimited)
  • Unicode text
  • CSV (comma delimited)
  • Formatted Text (space delimited)
  • Text (Macintosh)
  • Text (MS DOS)
  • CSV (Macintosh)
  • CSV (MS DOS)

The “save as” option saves only the active sheet and not the entire workbook. Retaining multiple sheets in a workbook requires saving each sheet as a separate file.

Different Formats

Formatted Text (space delimited): This popular option saves files with a .prn extension and retains the format as it is, with the exception that any characters beyond the 240th character of the line wraps to the next line. When several rows on the same sheet contain text beyond 240 characters, the text begins wrapping at the row after the last row that contains text.

Tab-Delimited Text: This option produces a file of ASCII characters and saves it with a .txt extension. Each row in the table becomes a separate line that terminates with a carriage return (“Enter”) character, tab characters replace column divisions. This format can be converted back into Excel or used in a wide range of other applications readily. The main drawback in this format is the challenge to retain all information. Saving as .txt file will remove all text formatting, and convert all formulas and functions on the sheet to their numeric values. Where text is rounded up or down by formatting options, the rounded-off value will save rather than the true value.

Text (Macintosh) and Text (MS-DOS) are variants of the tab-delimited format, applicable for the respective operating systems, which are now largely obsolete.

Unicode Text: The Unicode text option produces an output similar to tab-delimited text, but allows for the inclusion of non-ASCII characters such as Chinese, Arabic or Hebrew letters, which otherwise appear as question marks. To insert these characters in Excel, use the Insert / Symbol dialog box. Change the font setting to Normal Text and the From setting at the bottom right to “Unicode.” This will approximately double the size of the resulting file, as each character requires sixteen rather than eight bytes of storage space.

CSV (Comma-delimited): CSV stands for Comma Separated Values, and in this format each column divider is represented by a comma character: For instance, a row containing the name of the months would read in the text file as: “,Jan,Feb,Mar,Apr,May,Jun…” This applies even when columns are blank. A major problem with this format is confusion that arises when a comma is included as part of the text. There is no way to distinguish a comma which is part of the text and a comma which separates two different columns.

Comma-delimited text files are saved with the extension .CSV, and have the advantage that they can be double-clicked to open directly in Excel, whereas .TXT files need to be opened from within the program.

CSV (Macintosh) and CSV (MS-DOS) are variants of CSV that work on the respective operating systems, now largely obsolete. These formats use an even more restricted character sets than ASCII. These relate to older computing platforms and are now largely obsolete.

References