Excel - Delimited Text File Importing in Excel: Avoiding Annoying Behaviors & Gotchas - Episode 459

1,910 views · Published 4 June 2009 · 3:26 · Indexed 4 November 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Importing Delimited Files

Welcome to another episode of the MrExcel netcast! I'm Bill Jelen and today we're continuing our discussion on how to import text files into Excel. In this video, we'll be focusing on delimited files and how to properly import them into Excel.

If you recall from our previous video, we discussed the difference between fixed width and delimited files. Delimited files have a specific character, such as a comma, that separates each field. When we open a delimited file in Excel, the text import wizard automatically launches and prompts us to specify the delimiters.

There are four built-in delimiters in Excel: tab, semi-colon, comma, and space. In this example, we'll be using a comma as our delimiter. However, it's important to note that sometimes a programmer may use a different delimiter, such as a pipe character. In that case, we can click the "other" button and specify the delimiter ourselves.

One important setting to pay attention to is the option to treat consecutive delimiters as one. This is useful when we have empty fields, such as in the case of an apartment number. If we leave this option unchecked, Excel will ignore the empty field and keep the data in the correct columns. However, if we check this option, Excel will combine consecutive delimiters and potentially mess up our data.

Once we've specified our delimiters, we can click the "finish" button and our data will be imported into Excel. However, there is one thing to be aware of. After using the delimited text import method, Excel will remember this setting until we close the program. This means that any time we copy and paste data into Excel, it will automatically split the data into different columns based on the delimiter we used. This can be frustrating, but there is a workaround.

If we simply open the text file using the file open option and go through the text import wizard, this behavior will not occur. It only happens when we use the data text to columns feature throughout the day. So, if you're experiencing this issue, try using the file open option instead.

Thanks for tuning in to this episode of the MrExcel netcast. We hope you found this information helpful and we'll see you next time for more Excel tips and tricks. Don't forget to like and subscribe for more content from MrExcel!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/



How to import a delimited text file into Excel. Learn the gotchas associated with this type of file, including the annoying feature that gets turned on for the rest of the day after importing the text column. Episode 459 show you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Table of Contents:
(00:00) Introduction
(00:15) Delimited File Overview
(00:25) Difference Between Fixed Width and Delimited
(00:35) Opening Delimited File in Excel
(00:45) Using Text Import Wizard
(00:55) Specifying Delimiters
(01:06) Treating Consecutive Delimiters
(01:29) Finishing the Import
(01:44) Important Note
(02:00) Annoying Behavior
(02:43) Another Important Note
(03:06) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Annoying behavior in Excel
Consecutive delimiters in Excel
Data text to columns in Excel
Delimited text file
Delimiters in Excel
File open in Excel
Import text files into Excel
Importing data into Excel
Leading 0 on zip code in Excel
Pasting data in Excel
Text import wizard
Text to Columns in Excel

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152444/

More from this channel