Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Faster Text File Conversions.

Faster Text File Conversions

Written by Allen Wyatt (last updated August 4, 2021)
This tip applies to Excel 2007, 2010, 2013, and 2016


2

Pat wondered how to change the default column data type from “general” to “text” for all columns of a comma-delimited text file. Changing the format of each column, especially when there are many of them, can be tedious at best.

Unfortunately, there is no way to change the default. However, the changing of the column data types can be done much more easily by applying a little of the “pick and choose” features available in most Windows programs. Follow these steps:

  1. Start to import your comma-delimited text file as you normally would.
  2. When the dialog box is displayed that allows you to change column data types, select the first column in the table.
  3. Scroll to the right in the dialog box so the last column in the table is visible.
  4. Hold down the Shift key as you click on the last column. Now all the columns should be selected.
  5. Change the data type to Text.
  6. Continue with the import, as usual.

If you prefer an even faster way of inputting the information from the comma-delimited text file, you can do so using a macro, thereby skipping the Excel import filters entirely. The following macro, entitled (appropriately enough) Import, will do the trick:

Sub Import()
    Open "d:\data.txt" For Input As #1
    R = 1
    While Not EOF(1) 'Scan file line by line
        C = 1
        Entry = ""
        Line Input #1, Buffer
        Length = Len(Buffer)
        i = 1
        While i <= Length 'split string into cells
            If (Mid(Buffer, i, 1)) = "," Then
                With Application.Cells(R, C)
                    .NumberFormat = "@" 'Text formatting
                    .Value = Entry
                End With
                C = C + 1
                Entry = ""
            Else
                Entry = Entry + Mid(Buffer, i, 1)
            End If
            i = i + 1
        Wend
        If Len(Entry) > 0 Then
            With Application.Cells(R, C)
                .NumberFormat = "@" 'Text formatting
                .Value = Entry
            End With
        End If
        R = R + 1
    Wend
    Close #1
End Sub

You should note that you can change the first line of the macro to represent the name of the file you are importing. You should also understand that this macro works on the simplest of comma-delimited text files. If the file was created with quote marks around each field (as is sometimes the case), then the macro will not give the desired results and would need to be changed to compensate for the quote marks. Or, as an alternative, you could simply use search for and remove the quotes after the macro is through importing the information.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7889) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Faster Text File Conversions.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Creating Usable Figure Captions

Many people add both images and figure captions within text boxes so they can be easily positioned within a document. ...

Discover More

Toggling AutoCorrect Settings

If you need to turn AutoCorrect on or off, there is no built-in way to easily do it in Word. You can create your own ...

Discover More

Changing How Excel Determines which Year to Use

When you enter a date into a cell and you omit the year, Excel helpfully adds the current year to the date. If you want ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Stopping Date Parsing when Opening a CSV File

Excel tries to make sense out of any data that you import from a non-Excel file. Sometimes this can have unwanted ...

Discover More

Invalid Names when Opening Workbook

Don't you hate opening a workbook and seeing error messages? If you see a message that some "invalid names" were detected ...

Discover More

Loading Unwanted Files at Startup

Imagine how painful it would be if every time you started Excel it tried to load all the files in your root directory? ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is seven minus 7?

2021-08-04 11:13:12

Mark

If you want a non-macro technique you could use Power Query/Get & Transform Data. Not only could the user set the data type as Text they could also add any required data clean-up as well. Also, rearrange and rename columns etc. There are many resources on the Internet.


2017-12-24 05:54:12

Alex B

In the non-macro solution the "dialog box in step 2" will only appear is the file name extension is not ".csv". If it is ".csv" you will need to change it to something else which would normally be ".txt".
If you find that you are doing this all the time you could break the association of the csv file type with Excel through the control panel.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.