IMPORTTEXT function
IMPORTTEXT reads a delimited text file straight into the grid with a formula and returns the contents as a spilled array, so the data refreshes without Power Query.
Example
=IMPORTTEXT("C:\data\sample.txt")
Expected result: n/a Not yet checked in desktop Excel
Syntax
IMPORTTEXT(path, [delimiter], [skip_rows], [take_rows], [encoding], [locale])
Arguments in [square brackets] are optional.
-
path(required) - Path or URL of the text file.
-
delimiter(optional) - Character separating columns.
-
skip_rows(optional) - Rows to skip at the start.
-
take_rows(optional) - Maximum rows to return.
-
encoding(optional) - Text encoding of the file.
-
locale(optional) - Locale for interpreting numbers and dates.
Returns: The file contents as a spilled array.
One IMPORTTEXT formula can fill more than one cell. When the result has several values, they spill into the cells below or to the right of the formula.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=IMPORTTEXT("C:\data\import-sample.txt",CHAR(9)) | Name | Local tab file |
=IMPORTTEXT("C:\nope\missing.txt") | #VALUE! | Missing file |
Watch out for
- Argument list is from early announcements; confirm in the formula tooltip.
- May need file permissions or trusted locations.
Where it works
- Excel
- IMPORTTEXT is only in Excel for Microsoft 365 and Excel for the web. Very recent 365 addition, possibly still rolling out; unsure of final argument names, so verify in your build.
- Google Sheets
- Done with a different function: `IMPORTDATA`. IMPORTDATA(url,[delimiter],[locale]) reads text or CSV from a URL.
Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .