Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Soon!)

Import Text and Text Files into Excel

I have a text file with over 2,000 names that look like the following:

Mr. Benoit Smith
120 Adam Street
Dorchester, MA 02124

Marcus Darbouze
1553 Blue Hill Avenue
Boston, MA 02126

American Public Health Initiatives
10 Fairway Street
Mattapan, MA 02126

Most consist of 3 lines some as many as 5. The text file has no
delimiters so that when I import/open it in Excel it becomes just one
very long column. I need to convert it or parse it into any other
database friendly importable format i.e., .dbf, .xls, .csv etc. What I
need is to convert it into a table with up to five header rows:

head1 head2 head3 head4 head5

Please help, I've been at it for way too many hours.

P.S. Please keep it simple if possible, Excel is all new to me.


Usually the paragraph is split into different cells during import and what is
the best way to ensure they copy onto a single cell.

Hi there!

I'm using Excel 2003 to do analyse large textfiles, to do this I regularly
imports text to Excel, but unfortunately I've realized that there are some
limitations when importing text files.

I have a text file which has more that 65536 lines (which is the maximum row
numbers in one Excel worksheet), when I import this in Excel I first fill one
Now - the great thing is that I should be able to import the rest of the
textfile on another worksheet, so I start the "Text Impor Wizard", but when
using the feature "Start import at row" - the maximum row number I can type
is 32767... :-(

Of course I can start deleting lines from my textfile - but...

Please - Would you plan on changing this, so that this scenario would be
possible to solve?

Best Regards!
Mikkel Randorff Hegnhøj

I am trying to import a table, separated by delimiters, from the web. The
table's data changes frequently. I can use text-to-columns to sort the table
into the proper columns, but don't want to have to do this every time I
refresh the data. Is there any way around this? Heres the table I'm talking


Thank you,


Which options do I have to import a text file to Excel, with more than
200.000 records.



I frequently have to import data from an accounts report txt file into Excel
2K, via the Text Import Wizard, fixed width mode. Is there any way to save
the column width settings for future re-use?

Does anybody know of a way to automatically fire the Text Import Wizard
in excel. What I am trying to do is create an asp page for my users
which contains links to reports. These reports are text files which
need to be imported into excel. By clicking on the link my users will
open the file in excel and be prompted with the Text Import Wizard to
format the columns. As we run many thousands of different reports I
think that it would be impossible to predefine the columns, so this
must be done by the end user when they open it.
I have tried naming the text file as .csv and .xls, but they just open
with everything in the first column.

I'm having some trouble in Excel. If I create a plain text file like the following:

=sum(a1:a2), =sum(b1:b2)

Nothing can be done as if in spanish is (SI), iserror is (ESERROR), and so on. This really sucks as I can't just install other software on the server nor make other modifications.

I don't know what Microsoft was thinking when they translated the versions; any Excel version should accept it's language commands and English commands, but well, that's too much to ask for...

Any good sugestions?


If I am in an excel spreadsheet and I try to open a .txt file, the test
Import wizard used to open so I could determine columns and data etc. Now,
when I try to open the file, the wizard does not open..excel opens with a new
spreadsheet. How do I get the wizard to open again? My firm re-imaged my
machine and all kinds of things are off.
Old Bob

This is probably wishful thinking, but is there a way to import one text file into Column A, for example, and another text file into Column B, so they line up next to each other ? They are large files, so I don't want to have to cut and paste, if possible.

Thanks for any help !

I can NOT import a simple comma delimited file below in to Excel, althought I
used " as text qualifier, and COMMA as delimiter. Excel (version 2003)
persistenly parse it as having 3 fields rather than having only 2 fields.

"Income - $10,000 - $19,999", "Income - $20,000 - $29,999"

Could you please enlighten me? Thanks!

I remember my original Excel, when I opened a txt file, I would be allowed to delineate where columns ended, and move the column markers around. I am using Execl 2000, and I am not allowed to set column separators, other than by specifying the delimiter character.

My problem is that I am importing an error log that only has spaces to separate columns, but also has spaces in the descriptive section that should be one column.

What happened to Excel allowing you to combine columns? Is there any way around this problem?

I want to import text files into excel. I need all columns to be imported as text, so as to keep them looking exactly as they are in the text file. I had no problem at all doing this manually, by choosing, in the import text wizard to import all columns as text, and not as general, which is the default. But I would like this to be done automaticaly. Is there any way to change the default for importing text to "text" instead of "general"?
Thanks to anyone who can help me on this.


I am trying to use MS Query to first combine text files into one table of data and then import it to Excel. I have a data with identical amount of columns and with identical column headers in every file.. what would be the best way to do this..? thanks!

Hi everyone,
I have imported a tab delimited text file into excel. One of the columns consists of a string of text - a comment section. Within this column, there are periods and for some reason it seems that Excel is treating these periods as row separators. Does anyone know how I can prevent Excel from separating the text into rows?
Thanks in advance for any insights.

I'm trying to import a text file into excel. The file is tab delimited and uses double quotations as a text qualifier. One of the fields in the text file is a note field and every now and then the person entering the note uses a carriage return (hits the enter key) while doing so. When this comes through in the text file it looks like this...

"this is a note.
this is the second line of the note"

My understanding is that everything between the two instances of text qualifers should be treated as a single value and the carriage return disregarded. However when I import it the data comes in exactly as it is above which causes field types and column headings to not match up.

Can anyone tell me a way around this short of manually deleting each carriage return?

I have numerous PDF documents in a folder (could convert them to JPEG or PNGs if necessary). I want to run a macro that imports each document into a master workbook so that each document has its own worksheet. I DO NOT want hyperlinks... I want to import them either as images in text boxes or any other way that allows me to view the image automatically when I go to the worksheet, instead of clicking a hyperlink. Thanks!

Is it possible to change the text import wizard defaults in excel 2003?

The goal is to change it so that it *always* picks delimited and to, by
default, have space be a "selected" delimiter.

None of the files I import are fixed width, although some of the "look"
that way to the import wizard, and all of them use spaces as delimiters.


Bruce Bowler | As the economy gets better, everything else gets
1.207.633.9600 | worse. - Buchwald's Law
bbowler@bigelow.org |


I have a pipe delimited file, which has commas in some of the text. This file that I am importing is a .txt file and I am finding that when I import it all of the information is in the first column just how it should be apart from when there is commas in any of the sentances of the information that I had imported it places the text after the comma in the next cell.

How can I stop that from happening or how can I change it back to commas from tabs.

I hope you can help me here, as I have tried doing a script for it using PHP, but I am not having much success at doing that either.



I have a comma seperated value text file and want to import to Excel (easy) but I want each row of the text file to be inserted into a specific row of the spreadsheet based on the first value in each row of the text file.

Why I need this (for info)...

The text file is produced from web form submissions and so rows differ month on month (different people submitting at different times).

My first worksheet contains the imported text file and subsequent worksheets are linked to each row of the first.

Get it?

Is this possible?


I import a text file that some cells have blanks because there suppressed
repetative data.
Meaning if column a had


I want someway to fill blanks like

I know if I import into excel I could select data edit goto special blanks
enter = in active cell point to cell above and press CTRL + ENTER simultainiously.

The text file is too large for excel so I need that function in Access.
Thank you for your anticipated response.


I am writing a macro that changes the filepath for an existing text file import as a QueryTable. I want to set the connection parameters to variables and reapply them with a different filepath.


Dim DataType() as Variant
Dim Platform as Integer
Dim TableCell As Range
Dim TabDelimiter as Boolean, CommaDelimiter as Boolean

Set TableCell = Wksht.Range("A10")           
ReDim DataType(1 To UBound(TableCell.QueryTable.TextFileColumnDataTypes))
DataType = TableCell.QueryTable.TextFileColumnDataTypes
TabDelimiter = TableCell.QueryTable.TextFileTabDelimiter
CommaDelimiter = TableCell.QueryTable.TextFileCommaDelimiter
Platform = TableCell.QueryTable.TextFilePlatform

With Wksht.Range("A10").QueryTable
    .Connection = ConnectPath
    .TextFileColumnDataTypes = DataType
    .TextFilePlatform = Platform
    .TextFileParseType = xlDelimited
    .TextFileTabDelimiter = TabDelimiter
    .TextFileCommaDelimiter = CommaDelimiter
End With

This works fine for all parameters except TextFilePlatform. I want to set it to the code page (eg. 437 for IBM-PC, 65000 for Unicode)

VBA help says that

XlPlatform can be one of these XlPlatform constants.

and .QueryTable.TextFilePlatform has a number that refers to one of these constants.

However, the macro recorder assigns a code page number to the TextFilePlatform property.

How do I assign a variable to original code page and not have it reset to the default Windows (ANSI)?

I have a text file that has about 50 fields and I am importing it into a table that has just 10 fields. The first time that I import it, I import it into a New Table. I skip over 40 of the fields. This works fine. I end up with exactly the fields that I want. And I save the import specification so I can use it again.

The next time I try to import the file (e.g., the next day), I do /File/Get External Data/Import and then in the Import Text Wizard I press the Advanced button then the Specs button and select the Import spec that I want to use. It shows all the field names and has the tick boxes showing that most of them will be skipped. I say OK and proceed. Everything seems to go fine until I say I want to store my data in an existing table and name it. Access then gives an error message saying that the first field on the text file does not exists in the table and then says that the data was not imported. Now that field was specified to be skipped. So why is this causing a problem?

Hope you can help me how to code the following
How to import a csv file where it should only import only columns with a specific text (header) as it can sometimes differs from columns

Eg in cell

A1 Client Name
B1 Contracted
C1 Address
D1 Sales
Lets say I want only the columns Client Name, Sales. Etc imported to save speed and space

Can anyone help me pls?


I have a file exported from an accounting system that has no extension. The type just says "file". When I had Windows XP, I added the .txt extension (in the file name itself without opening) and was able to import the file into Access without issue. With Windows 7, it seems that this practice leads to the following error when the file is imported to Access:

"The changes you requested to this table were not successful because they would create duplicate values in the index, the primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

I know that none of the fields of the destination table are indexed and there is no primary key. The table has not changed in between the windows conversion. In addition, I get the same error if I import into a new table.

My current work around is to open the .lis file in Excel and save it as text with no further modification to the file. When this is done, the file imports flawlessly. I would like to avoid this as the file is very large and it takes some time to open.

I've searched this out quite a bit and haven't found a very good answer, other than something related to the primary key or index (which again i do not have).

Any help or suggestion is greatly appreciated.

I've been googling around on ways to import multiple text files without just specifying the directory in the macro. I sort of tried to combine two codes together, one that lets me select the text files, and one that lets me put all the data onto one spreadsheet, but it's not working. Any ideas?


Sub CombineTextFiles()
    Dim FilesToOpen
    Dim x As Integer
    Dim wkbAll As Workbook
    Dim wkbTemp As Workbook
    Dim sDelimiter As String
    Dim myDir As String, fn As String, txt As String, y, delim As String
    Dim a(), n As Long, t As Long, maxCol As Integer
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    sDelimiter = "|"
    FilesToOpen = Application.GetOpenFilename _
      (FileFilter:="Text Files (*.txt), *.txt", _
      MultiSelect:=True, Title:="Text Files to Open")
    If TypeName(FilesToOpen) = "Boolean" Then
        MsgBox "No Files were selected"
        GoTo ExitHandler
    End If
    ReDim a(1 To Rows.Count, 1 To Columns.Count)
    delim = vbTab
    fn = Dir(FilesToOpen & "\*.txt")
    Do While fn  ""
        txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(FilesToOpen & "\" & fn).ReadAll
        x = Split(Replace(txt, vbCrLf, delim), delim): t = t + 1: n = 1
        a(t, n) = fn
        For i = 0 To UBound(y)
            n = n + 1: a(t, n) = y(i)
        maxCol = Application.Max(maxCol, n)
        fn = Dir
    ThisWorkbook.Sheets(1).Range("a1").Resize(t, maxCol).Value = a
    Application.ScreenUpdating = True
    Set wkbAll = Nothing
    Set wkbTemp = Nothing
    Exit Sub
    MsgBox Err.Description
    Resume ExitHandler
End Sub

I get a type mismatch error

I have a text file in notepad with 4 rows for each set of data. Is there a way for me to get the data all into 1 row in the text file or all into 1 row in excel.




Currently I need a macro to import text file into a excel file and this is the code below. However every time when the text file is import, a new excel file will be created, can anyone help me to change the code, so that the text file will always import into the same excel file name " monthly standard working file".

      Dim ResultStr As String
      Dim FileName As String
      Dim FileNum As Integer
      Dim Counter As Double
      FileName = InputBox("Enter the Text File's name, e.g. test.txt")
      If FileName = "" Then End
      FileNum = FreeFile()
      Open FileName For Input As #FileNum
      Application.ScreenUpdating = False
      Workbooks.Add template:=xlWorksheet
      Counter = 1
      Do While Seek(FileNum) <= LOF(FileNum)
          Application.StatusBar = "Importing Row " & _
             Counter & " of text file " & FileName
          Line Input #FileNum, ResultStr
          If Left(ResultStr, 1) = "=" Then
             ActiveCell.Value = "'" & ResultStr
             ActiveCell.Value = ResultStr
          End If
          If ActiveCell.Row = 65536 Then
             ActiveCell.Offset(1, 0).Select
          End If
          Counter = Counter + 1
      Application.StatusBar = False
      End Sub

Hi there,

I need some help on a Macro. I have more than 160 Text files, I need to copy the text files into seperate sheet of excel. You all know it is a time consuming.

Today I google for the macro, luckly i found this one:


Sub CombineTextFiles()
    Dim FilesToOpen
    Dim x As Integer
    Dim wkbAll As Workbook
    Dim wkbTemp As Workbook
    Dim sDelimiter As String

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    sDelimiter = "|"

    FilesToOpen = Application.GetOpenFilename _
      (FileFilter:="Text Files (*.txt), *.txt", _
      MultiSelect:=True, Title:="Text Files to Open")

    If TypeName(FilesToOpen) = "Boolean" Then
        MsgBox "No Files were selected"
        GoTo ExitHandler
    End If

    x = 1
    Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
    Set wkbAll = ActiveWorkbook
    wkbTemp.Close (False)
    wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
      Destination:=Range("A1"), DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=False, _
      Tab:=False, Semicolon:=False, _
      Comma:=False, Space:=False, _
      Other:=True, OtherChar:="|"
    x = x + 1

    While x

Hi everyone,

I have put together a macro that will successfully loop through text files in a given folder and import all the data within each file into an excel sheet. The data comes from MS Word forms. The data from the forms are then saved comma-deliniated text files.

The problem I face is that if anyone uses a return carriage when inputting their data in the Word form (before transformation into the text file) then during the import into excel at each return carriage a new row is begun. Therefore as a precautionary mesaure I am forced to open each text file, search for any return carriages and replace them with a space so that each text file will only occupy one row.

Can anyone assist me in resolving this issue in vba? I have thought of two possible options: (1) open each text file, perform the replace and then save it again replacing the old file; (2) somehow indicating to import to ignore all return carriages. I don't know which is more feasible (or is a better approach). Also if anyone has any ideas on how to make the code more efficient I would happy to hear their thoughts.

Thanks in advance.


Hi,I am looking for Excel VB code where i can import data from multiple text(notepad) files into multiple worksheets of a new excel workbook. Also the name of the tabs should be as the names of the text files.Any Suggestions???

I have tried to import some data from Access to Excel and the text in the larger fields are being cut off. How do I fix this?

Hello again everyone.

I've got another problem that is over my head. I'm trying to take a MODFLOW output file(basically just a text file) that is ~ 9 million lines and extract only a set portion of data from it for import into excel.

The data that I need from the text file always begins at the line that starts with

 {14 spaces}HEAD IN LAYER{3 spaces}1 AT END OF TIME STEP

Note: the {14 spaces} is just that, 14 blank characters. The time step number changes throughout the file so there is other text after this, but the beginning is always those characters. I need the data from that line through the next 13565 lines copied/imported into excel.

Once this data has be imported, I would like to continue to move through the text file until the next occurrence of

 {14 spaces}HEAD IN LAYER{3 spaces}1 AT END OF TIME STEP

is found and repeat the procedure, only importing it into a new worksheet. This would continue until I reach the end of the file.

I did a search and found this code, but the whole reading from a text file is so foreign to me I'm not sure I know what it is doing.


Sub snb()
  Open "C:\ExcelTemp\M4A00P13.out" For Input As #1
    sq = Split(Input(LOF(1), #1), "              HEAD IN LAYER")
  Close #1
  For j = 0 To UBound(sq)
    With ThisWorkbook.Sheets.Add
      sn = Split(sq(j), vbCrLf)
      .Cells(1).Resize(UBound(sn) + 1) = Application.Transpose(sn)
    End With
End Sub

Any help or direction is greatly appreciated. Thanks all.

Hi all, I need to know how to avoid WebQuery to recognize formats on the fly, and just import everything as text.

Because of this "feature" Im loosing data when querying a web page, and must fix it manually...

For example, in the web page there is a number like "500.000" but after query it land as "500" in worksheet ( point "." is thousands separator) so i need the query to get it as text so i dont loose any digits.

Some things Ive tried already and no working:
- Search many forums and google, cannot find working solution.

- Setting cells format to text prior to query. Not working (excel will change format to number anyway)

- Change format to Text after query. Not working. Digits are lost for good, so nothing to do after the query.

- .WebDisableDateRecognition. This works only for dates, not for numbers.

- .PreserveFormatting. Not working .Tried true and false, no difference.

- .WebFormatting. Not working. All combinations, same result.

- Change Windows thousand separator in Control Panel. This might work, but code wont be fully portable to other users using different configuration.

So problem is very simple, i need web query to import EVERYTHING as plain text. Dont need Excel to be smart in this case...

Thanks in advance!!



I'm using Excel 2007

Every time I use the Text Import Wizard, it seems to deafult certain settings like the Delimiter as Space - is there anyway to change the default settings? I never use spaces in this and its irritrating to have to constantly change this.

Similarly, it always sets the default column type to General, is there an option to change this to Text (or whatever) before importing?

Thanks in advance


There are a lot of times where I would need to save data in a text file before I can use the "Get External Data: From Text".

How can I import my clipboard so the text import wizard can be used?

I have a data file that I need to download and convert from text to xls to then do further manipulation. When I manually fire up excel, open the text file and leave all the text import wizard defaults I can then work with the resulting xls file programmatically. When I use the following code I get errors when I try to open the resulting xls file. I haven't been able to find much about the different options in the OpenText file command so I'm not sure what properties I'm setting incorrectly that the wizard automatically sets correctly. It's a tab delimited file. Can anyone tell me what options I need to set to get it converted correctly? Thanks.


Excel.Application application = new Excel.Application();
                object missing = System.Reflection.Missing.Value;
                System.Threading.Thread.CurrentThread.CurrentCulture = new 
                System.Threading.Thread.CurrentThread.CurrentUICulture = new 
                application.Caption = "Test";
                application.Visible = true;
                    true,  //TAB
                    false, //COMMA

                application = null;


Is there anyway to invoke the Import Text Wizard from the command line ? What I am looking to do is something like

excel myfile.txt /invokeTextWizard


im looking for a macro that will use the import feature that will import text and delimit it.

i want to manually choose the file. all i know is workbooks.open filename: function, not the import

My question is is it possible to automate the Text Import Process. The reasons why I want to do this is I get approximately 25 ".txt" files daily which I currently manually import into excel using the wizard which is time consuming and repetitive then I have to formatted and saved a an ".xls" file. I would like to automate the formatting part too.

Examples of the files names below


See Post "VBA to removed unwanted rows" Posted on 19/04/201009:23 PM full detail of the above files.

Any assistance will be greatly appreciated.

How can I import a whole text field into a single cell in Excel, without the line breaks making Excel start a new row? I'd like the whole text document in a single cell.

I know I could copy and paste but I want to automate it.

Eventually, I would like to write a script to go through a whole directory and import each text file into a different cell of my spreadsheet. But small steps...

Hi Experts,

I need to import certain lines from a large text file, say 79496 lines or even may become more in future. I would like to import only certain rows from the file, e.g. Line 79196 to the bottom. I tried to record a macro to get the codes, but found that the StartRow value for importing a text file could only be an integer, which means 79196 will not be a value that Excel allows. Do you have any idea how to do this? Thanks!

p.s. Considering the appending size of this text file, I really don't want to import the file as a whole.

I am trying to import a text file into excel.

Right before the import takes place, I get a dialog box that states:

the text file contains more data than will fit on a single worksheet.

To continue and import as much of the data as will fit, click ok. You can then import the rest of the data by repeating the import operation on another worksheet and using the text import wizard to exclude data already imported.

Is there anyway to code this with doing this automatically into the next sheet?

thanks for any help


Greetings and merry christmas for anyone celebrating it.

I want to open or import a text file. It has range of up to 10,000 and row to up to 200. I'm using excel 2003 at office.
I've tried import the data using text import wizard (try using excel 2007). However not all column can be displayed in step 3.

Is there anyway I could just open the text file and all data will be transposed?

If this issue has been raised please help me to where is it located?


I am importing a text file into access. I have to import it as 'fixed width' format. This does not give me the option of selecting the 'first row as headers', therefore the headers are field1, field2 etc and the actual headers from the text file import as the first data row.

Is there a way round this?, a quick way to change the data row to be the header row, or an update/append query solution.

Any help much appreciated.

I have space delimited text files that need to be imported into Excel. These files have several hundred columns, but no more than 100 rows. I need to import the files into Excel but cannot due to the column limitation. I am looking for some VB code that will transpose the files on import so the column limitation will not be an issue.


I have a large text file, 75mb, that I would like to import into Excel but it goes well beyond the 65536 lines available.

Is there a process to get these files into excel? File splitter?



I am trying to import information that I want in one cell but because
of multiple colons and semicolons it is automatically put in different
cells. Is there any way to turn this off and/or turn off the text
import wizard?

Hi Everyone,

I'm tryng to convert a text file that is comma separated into a csv. I've done this hundreds of times in the past but one thing really puzzles me and I'd appreciate any guidance on how I can fix this.

In the text file I have some bank details whic show sort code and bank account number. These are separted in teh text file correctly with commas as in teh example below.


When I open the text file using excel and import it. I select the format of Text for all columns and excel displays the data. Now to my problem, in the column where the sort code is contained "AH" (in the example above that is 11-11-11) Some of the cells show these as a date format such as 11/06/1941 while other cells show this information correctly formated as 11-11-11. I can see no logical reason why for example these cells format incorrectly


Those are just a few examples to show the apparent randomness of the issue. The text file itself looks 100% error free.

It's as if Excel ignores the format command on some cells during the import. Can anyone help me with this problem?

I'm using Excel 2007



Hi all,

Importing text files into excel is farely simple having googled round the topic. The text file i'm importing is awfully big though, so I wondered if I could just read in the columns I need to speed things up.

(I could import the whole thing then delete the columns I don't want after but that won't make the import stage any quicker)

So does anyone know how I can import just, say, the 1st,2nd, 4th and 10th columns for example?