IMPORTTEXT Function

The IMPORTTEXT function imports data from a text-based file into Excel and returns the result as a dynamic array that spills onto the worksheet. IMPORTTEXT can read tab-delimited (the default), comma-delimited, pipe-delimited, fixed-width, and other text formats from a local file path or a URL. Optional arguments let you skip rows, limit the number of rows returned, and control character encoding and locale-aware parsing.

Because IMPORTTEXT can be combined with other Excel functions, it is a simple alternative to Power Query for light import work on data sets that are modest in size. Note that IMPORTTEXT is different from most functions in that imported data does not refresh automatically. To pick up changes in the source file, click Refresh All on the Data tab.

Excel provides two functions for importing delimited text files: IMPORTTEXT and IMPORTCSV. IMPORTTEXT is the more robust function, with custom delimiters, options to take and skip rows from the start or end of a file, and encoding control. IMPORTCSV is a simpler variant hardwired to import comma-delimited text files only.

IMPORTTEXT is currently available to Microsoft 365 subscribers on the Insiders Beta channel.

Key features

• Reads from local file paths or URLs
• Returns a dynamic array that spills onto the worksheet
• Supports any single character, multi-character, or special-character delimiter (via CHAR)
• Parses fixed-width columns when delimiter is an array of integers
• Skips or limits rows from the top or bottom of the file (negative values)
• Configurable encoding (default UTF-8) and locale
• Does not auto-refresh; use Data > Refresh All to update

Table of contents

• Sample data
• Basic examples
• Import a CSV file
• Use a custom delimiter
• Skip and limit rows
• Fixed-width columns
• Handle international files
• Combine with other functions
• Import from a URL
• Refresh or freeze imported data
• Notes

Sample data

The examples on this page use the sample data files below. Each file contains a small set of fictional orders in a different format. You can download them to follow along with the examples.

The examples on this page use C:\data\ as a short placeholder for the file path. Replace this with the actual path to the downloaded files on your machine.

Basic examples

The minimum requirement is a file path. With no other arguments, IMPORTTEXT reads the file as tab-delimited, UTF-8 encoded:

=IMPORTTEXT("C:\data\orders.txt") // tab-delimited

For a comma-delimited file, supply a comma as the delimiter argument:

=IMPORTTEXT("C:\data\orders.csv",",") // comma-delimited

To skip the first row (typically a header):

=IMPORTTEXT("C:\data\orders.txt",,1) // skip first row

To import only the first 10 rows of a file:

=IMPORTTEXT("C:\data\orders.txt",,,10) // first 10 rows only

To import only the last 10 rows of a file:

=IMPORTTEXT("C:\data\orders.txt",,,-10) // last 10 rows only

Import a CSV file

The default delimiter is tab. To import a comma-separated (CSV) file, supply a comma as the delimiter argument. The first few rows of orders.csv look like this:

OrderID,Date,Customer,Product,Quantity,Amount
1001,2026-01-05,Acme,Mouse,12,287.88
1002,2026-01-08,Globex,Cable,50,449.50
1003,2026-01-12,Initech,Stand,8,239.92
...

In the worksheet below, the formula in cell B4 imports the file:

=IMPORTTEXT("C:\data\orders.csv",",")

The result is a dynamic array of 11 rows by 6 columns that spills from B4. The first row contains the column headers from the file.

If your file is comma-delimited and UTF-8 encoded (the most common CSV setup), IMPORTCSV is a simpler alternative. It is a variant of IMPORTTEXT with CSV defaults hard-coded.

Use a custom delimiter

The delimiter argument accepts any single character or multi-character string. The file orders-pipe.txt uses a pipe (|) as the column separator:

OrderID|Date|Customer|Product|Quantity|Amount
1001|2026-01-05|Acme|Mouse|12|287.88
1002|2026-01-08|Globex|Cable|50|449.50
1003|2026-01-12|Initech|Stand|8|239.92
...

To import the file, supply "|" as delimiter:

=IMPORTTEXT("C:\data\orders-pipe.txt","|")

For special characters that are hard to type or invisible (like a tab), you can use the CHAR function to provide a delimiter:

=IMPORTTEXT("C:\data\orders.txt",CHAR(9)) // tab (default)
=IMPORTTEXT("C:\data\orders.txt",CHAR(59)) // semicolon
=IMPORTTEXT("C:\data\orders.csv",CHAR(124)) // pipe

CHAR(9) returns the tab character, CHAR(59) is a semicolon, and CHAR(124) is the pipe character. See ASCII for a full list of character codes.

Skip and limit rows

Use skip_rows to skip rows at the top of the file. This is useful when a file contains metadata or extra rows above the data. The file orders-with-header-block.csv has three metadata rows before the column headers:

Quarterly Orders Export
Generated: 2026-02-06
Source: ERP System
OrderID,Date,Customer,Product,Quantity,Amount
1001,2026-01-05,Acme,Mouse,12,287.88
1002,2026-01-08,Globex,Cable,50,449.50
...

The formula in B4 skips the first three rows so the import starts at the column headers:

=IMPORTTEXT("C:\data\orders-with-header-block.csv",",",3)

To skip rows at the bottom of the file (like a totals row), supply a negative skip_rows value. The file orders-with-total.csv ends with a "Total" row:

OrderID,Date,Customer,Product,Quantity,Amount
1001,2026-01-05,Acme,Mouse,12,287.88
1002,2026-01-08,Globex,Cable,50,449.50
...
1010,2026-02-05,Piper,Marker,3,389.85
Total,,,,,4540.39

The formula in B4 skips the last row by providing -1 for skip_rows:

=IMPORTTEXT("C:\data\orders-with-total.csv",",",-1)

The take_rows argument limits how many rows are returned. To extract just the first 5 rows of a file:

=IMPORTTEXT("C:\data\orders.csv",",",,5)

A negative take_rows value takes rows from the bottom instead of the top. To return the last 3 rows:

=IMPORTTEXT("C:\data\orders.csv",",",,-3)

Fixed-width columns

For files where columns are aligned by character position instead of separated by a delimiter, supply an array of ascending integers as the delimiter argument. Each integer is the zero-indexed character offset where a column begins. The sample file orders-fixed.txt looks like this, with columns aligned by character position:

OrderID Date        Customer  Product   Quantity  Amount
1001    2026-01-05  Acme      Mouse     12        287.88
1002    2026-01-08  Globex    Cable     50        449.50
1003    2026-01-12  Initech   Stand     8         239.92
...

The columns begin at character offsets 0, 8, 20, 30, 40, and 50 (the first column always begins at offset 0). The formula in B4 supplies those offsets as an array:

=IMPORTTEXT("C:\data\orders-fixed.txt",{0,8,20,30,40,50})

IMPORTTEXT uses these positions to slice each row into columns. Trailing spaces are trimmed automatically.

Note that the integer array provided for delimiter uses zero-indexed offsets, which is unusual compared to other Excel functions (MID, for example, is 1-based).

Handle international files

Files exported from European systems often use semicolons as the column delimiter, commas as decimal separators, and DD.MM.YYYY date formats. The sample file orders-semi.csv uses all three conventions:

OrderID;Date;Customer;Product;Quantity;Amount
1001;05.01.2026;Acme;Mouse;12;287,88
1002;08.01.2026;Globex;Cable;50;449,50
1003;12.01.2026;Initech;Stand;8;239,92
...

Combine delimiter=";" with the locale argument so IMPORTTEXT parses numbers and dates correctly:

=IMPORTTEXT("C:\data\orders-semi.csv",";",,,,"fr-fr")

Note (August 2026): the latest Insiders Beta builds insert a new sixth argument, formatting options, before locale. This argument is not yet documented by Microsoft. On these builds, add one more comma before the locale code so that it remains the last argument: =IMPORTTEXT("C:\data\orders-semi.csv",";",,,,,"fr-fr"). I will update this page when the new argument is officially documented.

Without locale (on a machine running in English in the United States) the Amount column would import as text (because the comma would be treated as part of the value) and dates would not be recognized. With locale set to "fr-fr", Excel parses 287,88 as the number 287.88 and 05.01.2026 as a real date. The locale argument is optional and defaults to the operating system locale.

A locale code identifies a language and a region together, in the form language-region. The language is a two-letter code like en, fr, or de, and the region is a two-letter country code like us, gb, or br. Common examples include "en-us" (US English), "en-gb" (UK English), "de-de" (German in Germany), and "pt-br" (Portuguese in Brazil).

The encoding argument (the fifth argument) controls how the file's raw bytes are interpreted as characters. UTF-8 (the default) is the modern standard and handles virtually all text, including accented characters and emoji. Other supported encodings include UTF-16 (used by some Windows tools) and Windows-1252 (sometimes labeled ANSI, common in older CSV exports from Excel for Windows).

In most cases you'll never need the encoding argument. UTF-8 covers plain ASCII, UTF-8 with BOM (Excel strips the BOM), and anything you'd export from a modern tool. The argument exists mainly for legacy files. If text imports look right, the default is fine. If you see garbled characters (for example, é instead of é), try "utf-16" or "windows-1252".

Combine with other functions

Because IMPORTTEXT returns a dynamic array, the result can be passed directly to other dynamic array functions. To import orders.txt and sort the rows by Amount (column 6) in descending order:

=SORT(IMPORTTEXT("C:\data\orders.txt",,1),6,-1)

The skip_rows argument of 1 drops the header row, then SORT orders the remaining rows by the sixth column, descending. Note the header values in row four are hand-entered, and the formula is one row down in cell B5. The same pattern works with FILTER, CHOOSECOLS, UNIQUE, and GROUPBY. For light import work, this makes IMPORTTEXT a simple alternative to Power Query. For an overview of dynamic arrays in Excel, see Arrays in Excel.

Import from a URL

The path argument also accepts a URL. To import the orders.csv sample file hosted on this page:

=IMPORTTEXT("https://exceljet.net/functions/importtext-function/data/orders.csv",",")

When a URL requires authentication, Excel prompts for an authentication method (Anonymous, Windows, Basic, Web API, or Organizational account). Once entered, credentials are saved and can be managed under Data > Get Data > Data Source Settings.

All of the sample data files listed in the Sample data section above can be imported the same way by replacing the filename in the URL.

Refresh or freeze imported data

Unlike almost all other functions in Excel, IMPORTTEXT does not refresh automatically. To re-import the latest data, click Refresh All on the Data tab. Note that if the specified file has been moved or renamed, IMPORTTEXT will return a #VALUE! error.

For a one-off import where the data should not change again, replace the formula with static values in the standard way: Select the spilled range, copy it, then use Paste Special > Values (or Home > Paste > Paste Values) to overwrite the formula. The result is now disconnected from the source file and will not change if the file is later modified, moved, or deleted. This is also useful when sharing the workbook with someone who will not have access to the source file.

Notes

• IMPORTTEXT is currently available only to Microsoft 365 Insiders Beta subscribers.
• Imported data does not refresh automatically. Click Refresh All on the Data tab to update.
• The default delimiter is the tab character.
• The default encoding is UTF-8.
• The locale argument controls how dates and numbers are parsed, not how they are displayed.
• For comma-delimited UTF-8 files, IMPORTCSV is a simpler variant of IMPORTTEXT.
• File paths can be local (e.g., C:\data\orders.csv) or URLs starting with http:// or https://.
• Negative skip_rows and take_rows values count from the bottom of the file.