I am using a CSV to import data.

The column of data has different and sometimes multiple numbers in one field.
For example...

2924,2925, 2926 in one field
2927,2928, 2929 in another field.

I have been using the format feature to save it as 'Text' and then importing.
The problem is when I reopen the CSV, it is not formatted as 'Text' anymore and all my numbers are mangled.

Any idea how to keep that column formatted as Text when re-opening the CSV?


Open it through the Data \ Get External Data \ From Text tool, where you can specify the data type of columns.

There is a full description of the process on the Office website. If you prefer pictures, you can check out this site instead.


In Excel, it is possible to save text import settings (as a "template") and reuse them with any text file later. A blogpost by Excel MVP Jan Karel Pieterse describes this in detail: http://blogs.office.com/2010/06/10/importing-text-files-in-an-excel-sheet/

The "text import template" is automatically created when you are done with Text Import Wizard. Make sure that you save your workbook to keep the "template".

To reuse it with another (or the same) text file:

  1. Click any cell within your sheet that holds (used to hold) data of a previous import.
    • Excel 2007/2010: Click Data -> Refresh All dropdown -> Refresh
    • Excel 2003 and older: Click Data -> Refresh Data

If you use this "template" to always import a file of the same name and location, you can customize the "template" so that Excel does not prompt you for a file name each time you hit the Refresh button.

During the text import, click Properties... in the Import Data dialog and untick Prompt for file name on refresh in the External Data Range Properties dialog. You can also edit that later in the Connection Properties dialog.

I would post screenshots, but I "need at least 10 reputation to post images."

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.