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
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:37
Excel - Enhance Your Excel Charts: Add a Picture as a Background in Excel - Episode 917
-
2:00
Excel Hiding Data in Plain Site - Episode 919