Master Advanced Excel & VBA

Learn Excel formulas, data analysis, dashboards, VBA programming, and business automation through structured practical lessons.

COURSE SYLLABUS
1. Excel Basics
Introduction to Advanced Excel Workbook, Worksheets, Rows & Columns Data Types & Cell Formatting Cell References & Keyboard Shortcuts
2. Advanced Excel Formulas
Advanced Formulas Introduction IF, AND, OR & IFERROR Excel Tables XLOOKUP INDEX & MATCH SUMIFS & COUNTIFS
3. Pivot Analytics
Pivot Analytics Introduction Pivot Tables Calculated Fields & Items Timelines & Slicers Text Functions & Data Cleaning
4. Excel Dashboards
Interactive Dashboards Introduction Data Validation Conditional Formatting Combo Charts Sparklines
5. Excel Data Tools
Data Integration Introduction Importing Text Files Power Query Worksheet Protection Formula Auditing
6. VBA Fundamentals
VBA Fundamentals Introduction Macro Recorder VBE Workspace VBA Objects & Properties Variables & Data Types MsgBox & InputBox If...Then...Else Select Case Loops & Dynamic Ranges
7. VBA Automation
VBA Automation Introduction Subroutines & Arguments User-Defined Functions Workbook Events Worksheet Events Form Controls & Buttons
8. VBA UserForms
UserForms Introduction UserForm Design Toolbox Controls Input Validation CRUD Operations with UserForms
9. VBA Optimization & Security
Optimization Introduction Error Handling Debugging Tools Performance Optimization VBA Project Security
10. Corporate Capstone Project
Capstone Project Introduction Data Consolidation Data Cleaning & Analysis Automated PDF Reports Dashboard Automation
11. Excel Practice & Assignments
Practice Introduction Excel Formula Exercises Pivot Table Exercises Dashboard Exercises VBA Programming Exercises Final Assignments

Importing Text Files in Excel

Learn how to import TXT and CSV files into Excel, select delimiters, handle text columns correctly, and prepare imported data for analysis.

1. What Is Text File Importing?

Text file importing is the process of bringing data stored in a text-based file into an Excel worksheet. Text files commonly contain records separated by commas, tabs, semicolons, or other delimiters.

  • .txt: A text file that may use tabs or another delimiter.
  • .csv: A comma-separated values file, although the delimiter can vary.
  • Delimited data: Values are separated by a chosen character.
  • Fixed-width data: Values occupy defined character positions.
Remember: Importing a text file is not the same as opening a formatted Excel workbook. Text files do not normally preserve Excel formatting, formulas, or worksheet structures.

2. Understand Text File Formats

Image 1 — TXT and CSV File Examples
students.csv StudentID,Name,Course 101,Samadhan,Java 102,Samiksha,SQL students.txt StudentID Name Course 101 Samadhan Java 102 Samiksha SQL Excel Worksheet StudentID Name Course 101 Samadhan Java 102 Samiksha SQL

Custom illustration showing how delimited text becomes worksheet columns.

3. Prepare a Sample CSV File

Open Notepad or another plain-text editor and enter the following data. Save the file as Students.csv.

Students.csv
StudentID,StudentName,Course,FeesPaid
101,Samadhan,Java,45000
102,Samiksha,SQL,60000
103,Pradnya,Python,32000
104,Omkar,C#,75000
105,Neha,Java,28000
What Does a Comma Do?

In this example, commas separate the fields. Excel can use the comma delimiter to split each record into four columns.

Important: When saving from Notepad, check the file name and extension. If needed, choose All Files in the Save As dialog so the file does not accidentally become Students.csv.txt.

4. Import a CSV File Using Excel

Image 2 — Excel Data Import Steps
1 Data Tab Get Data Import Tools 2 From Text/CSV Browse Files Select CSV 3 Preview Check Columns Check Types 4 Load Worksheet Imported Data Data → From Text/CSV → Preview → Load Labels may vary depending on Excel version.
Step-by-step procedure
  1. Open Excel and create or open the destination workbook.
  2. Select the Data tab.
  3. Choose From Text/CSV or the equivalent available import option.
  4. Browse to Students.csv and select it.
  5. Check the preview, delimiter, and column data types.
  6. Select Load to place the data in Excel, or Transform Data to open Power Query.
  7. Verify that each field appears in the correct column.

5. What Is a Delimiter?

A delimiter is a character or separator that divides fields in a text record. Choosing the correct delimiter helps Excel split data into the intended columns.

Delimiter Example Typical Use
Comma (,) 101,Samadhan,Java Common CSV files.
Tab 101 [Tab] Samadhan [Tab] Java Tab-separated text files.
Semicolon (;) 101;Samadhan;Java Files using semicolons as separators.
Pipe (|) 101|Samadhan|Java Custom structured text exports.
Space 101 Samadhan Java Some text files; use carefully if values contain spaces.

6. Delimited vs Fixed-Width Text

Image 3 — Two Ways to Split Text
A. Delimited Text 101,Samadhan,Java 102,Samiksha,SQL Comma separates fields Choose the matching delimiter B. Fixed-Width Text 101 Samadhan Java 45000 Active Field boundaries are based on character positions.

Fixed-width fields are separated by positions rather than delimiter characters.

Delimited Files

Excel splits a record when it encounters the selected separator, such as a comma or tab.

Fixed-Width Files

Fields are separated according to character positions. During import, check the column boundaries carefully before loading the data.

7. Handle Column Data Types Correctly

Excel may interpret imported fields as numbers, dates, or text. Review the types before loading, especially for IDs, dates, and values that contain leading zeros.

Value Potential Problem Recommended Handling
00125 Leading zeros may disappear if treated as a number. Import identifiers as Text when zeros are significant.
17-10-2026 Date interpretation may vary by regional settings. Check the expected date format during import.
45000 May be imported as text in some cases. Convert to a numeric type if arithmetic is required.
Samadhan Extra spaces can affect matching. Trim unnecessary spaces during cleaning.
Important: Student IDs, postal codes, and similar identifiers are not necessarily quantities. Keep them as text when their formatting, such as leading zeros, must be preserved.

8. Import a Text File Using the Text Import Wizard

Some Excel versions and legacy workflows provide a Text Import Wizard. Its availability and entry point vary by version and settings.

  1. Select the appropriate text import command if available.
  2. Choose Delimited or Fixed Width.
  3. For delimited data, select the correct separator.
  4. For fixed-width data, adjust the column boundaries.
  5. Preview the columns and select suitable data formats.
  6. Choose the destination and finish the import.

In current Excel versions, the Data tab's text/CSV import flow is commonly used instead. Follow the options available in your installation.

9. Common Import Problems and Solutions

Problem Possible Cause Solution
All values appear in one column. Incorrect delimiter selected. Choose the delimiter used by the source file.
Leading zeros disappear. Identifier interpreted as a number. Import the relevant column as Text.
Dates appear incorrectly. Different date or regional format. Review the date format during import.
Special characters look incorrect. Encoding mismatch. Check the file encoding option, such as UTF-8, when available.
Columns shift unexpectedly. Quoted text contains delimiters or source data is inconsistent. Review the import preview and quote handling.
Numbers cannot be calculated. Numeric values imported as text. Check data types and convert valid numeric values.

10. Verify the Imported Worksheet

Image 4 — Imported Data Quality Checklist
Imported Students Table ID Name Course Fees 101 Samadhan Java 45000 102 Samiksha SQL 60000 103 Pradnya Python 32000 104 Omkar C# 75000 105 Neha Java 28000 Validation Checklist Headers correct Rows imported IDs preserved Types checked Values verified

Illustrative checklist. Verify these points against your actual imported file.

After importing, compare the row count and sample values with the original file. Check headers, leading zeros, dates, numeric columns, and unexpected blanks before using the data in reports.

11. Practical Exercises

Task 1: Import a CSV File

Create Students.csv using the sample records and import it into Excel with the correct comma delimiter.

Task 2: Import Tab-Delimited Text

Create a text file whose fields are separated by tabs. Import it and verify that each field appears in a separate column.

Task 3: Preserve Leading Zeros

Import IDs such as 00101 and 00102. Ensure that they remain unchanged by treating the ID column as text where appropriate.

Task 4: Fix an Incorrect Delimiter

Import a semicolon-separated file and choose the correct delimiter so that its fields split into the intended columns.

Important Points to Remember

  • CSV and TXT files may use different delimiters.
  • Check the preview before loading the imported data.
  • Keep identifiers as text when leading zeros are significant.
  • Verify date formats, numeric values, and character encoding.
  • Delimited and fixed-width files require different import settings.
  • Use Transform Data when cleaning or reshaping is necessary.
  • Validate imported row counts and sample values against the source.

Summary

Excel can import data from TXT and CSV files using supported text import tools. Correct delimiter selection, careful data type handling, and previewing the records help ensure that values are placed in the intended columns.

After importing, verify the headers, row count, identifiers, dates, and numeric values. These checks help prepare reliable data for Excel formulas, PivotTables, and reports.