How to Preserve Formulas When Saving as a Text File

MORE INFORMATION

When a Microsoft Excel file is saved as text file, only the active sheet and the displayed values are saved. All formulas that are on the active sheet are discarded. You can preserve the formulas when you save the file as a text file. To display formulas in the cells in the Microsoft Excel sheet, use either of the following methods:

• Press CTRL+TILDE (~).
  
  -or-
• In Microsoft Excel 98 Macintosh Edition, click Preferences on the Tools menu. (Click Options on the Tools menu in earlier versions.) Click the View tab. Then, click to select the Formulas check box. Click OK.

After the formulas are displayed in the cells, the file can be saved as a text file, thereby preserving both the values and formulas. When you reopen the file, the Text Import Wizard will prompt you for the original data type and whether the file is fixed-width or delimited. If you choose delimited for the file type and choose Tab as the delimiter, the formulas will be place back in their respective cells, and the values that they produce will be displayed.

Opening the file as a Tab Delimited file is the only way to preserve the formulas and values that were in individual cells on the original worksheet. All of the other methods force the text file to display its formulas when the Text Import Wizard is finished.