Importing text files with VBA – 2

Previous post

The text file import routines and functions presented in the previous post have been modified with the following additions:

Lines of text can be split into separate cells, at any designated character (e.g. a coma or a space)

The SplitText function can also be used as a UDF, to split lines of text previously imported.  In this case the most efficent way is to enter the UDF as an array function, operating on the complete range of text.  In this way the UDF is only called once, returning a 2D array, and the whole operation is much quicker.


Screen shot:



This entry was posted in Excel, UDFs and tagged , , , , . Bookmark the permalink.

6 Responses to Importing text files with VBA – 2

  1. Pingback: Importing text files with VBA « Newton Excel Bach, not (just) an Excel Blog

  2. Wib says:

    I have got problem with text file data logger due to data over 65k row. How if text file over 65K row, any solution code to import data to excell ? thank’s


  3. dougaj4 says:

    Wib – The easy answer is use XL2007, then you can import up to about 1 million rows with no problems. In XL2003 or earlier you need to split the data into two (or as many as required) arrays, then transfer each array into a different range.

    Alternatively do more processing of the array inside VBA to reduce the amount of data you need to transfer to the spreadsheet. That probably makes more sense in most cases, since a spreadsheet isn’t a particularly efficient way of presenting data when there are 100’s of thousands of rows of it.


  4. dougaj4 says:


    for a modified file supporting import of very big files and a few other improvements.


  5. Pingback: Daily Download 32: Text functions | Newton Excel Bach, not (just) an Excel Blog

  6. Pingback: Importing Text Files; Unix Format | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.