Home Courses Microsoft Excel Day 24: Text Functions — Part 2
WEEK 4 DAY 24 OF 30 60 MINUTES WORKBOOKS INCLUDED

Day 24: Text Functions — Part 2

Clean text and standardize formats: master UPPER, LOWER, PROPER, LEN character counting, and Flash Fill (Ctrl + E).

Easy-Learn Framework

Mastering Day 24: What, Why & How

Memory Rule Included
Day 24: Text Functions — Part 2 Visual Diagram
? WHAT Is It?

Precision surgical text extraction: =LEFT() pulls characters from the start, =RIGHT() from the end, =MID() from the middle, and =FIND() locates the exact character position.

! WHY Does It Matter?

Separating postal codes, extracting invoice prefixes, isolating area codes from phone numbers, or pulling year codes out of product serials like 'PROD-2026-948'.

⚙ HOW Does It Work?
Step 1 Extract prefix: =LEFT(A2, 4) extracts the first 4 characters.
Step 2 Extract suffix: =RIGHT(A2, 3) pulls the last 3 characters.
Step 3 Extract middle: =MID(A2, 6, 4) starts at character 6 and extracts 4 characters.
Memory Hook & Golden Rule (Remember This Forever):

"LEFT reads from the start, RIGHT reads from the end, MID dives into the middle. Just tell Excel where to start and how many characters to pull!"

Learning Objectives

What You Will Master Today

  • =UPPER(text) converts all characters to capital letters (essential for PAN and GSTIN)
  • =LOWER(text) converts all characters to lowercase (ideal for email addresses)
  • =PROPER(text) capitalizes the first letter of each word (standard for human names)
  • =LEN(text) counts exact number of characters including spaces (vital for auditing phone numbers and PIN codes)
  • Flash Fill (Ctrl + E) for automatic split, merge, and formatting without writing formulas
Important Keyboard Shortcuts:
Ctrl + E (Flash Fill Magic Shortcut)=LEN(text) (Count Characters)=UPPER(text)=LOWER(text)=PROPER(text)
Guided Tutorial

Step-by-Step Practical Walkthrough

Step 1

To standardize PAN card numbers to uppercase, write '=UPPER(C5)'.

Step 2

To audit whether a mobile number has exactly 10 digits, write '=LEN(D5)'.

Step 3

To test Flash Fill: Type the desired format in the first row (e.g. splitting 'John Doe' to 'John'), press Enter, and press Ctrl + E.

Step 4

Excel magically detects the pattern and populates the remaining rows!

Solved Classroom Example

Haldwani Business Registry - GSTIN & PAN Verification Ledger

Pre-filled Data & Answers

Study the completed reference worksheet below. Every column illustrates the standard formatting, formula structure, and alignment applied by professional office operators.

Firm NameRaw Input PANStandardized PAN (=UPPER())PAN Length (=LEN())Validity Status (=IF(LEN=10))
Kumaon Tradersabcde1234fABCDE1234F10Valid PAN
Haldwani Medical Storebmkpr5678kBMKPR5678K10Valid PAN
Nainital Sweetsaazpp901AAZPP9018Invalid (Missing Digits)
Shree Ram Enterprisesckkpt4321mCKKPT4321M10Valid PAN
Download Solved Sample Sheet Includes complete working formula cells, proper currency symbols, and clean borders.
Download Solved Sheet (.xlsx)
Student Assignment

Customer Contact Database Sanitization

Your Turn to Practice
Exercise Tasks Checklist:
  • Task 1: Type 'Rahul' in cell B5 and press Ctrl + E to extract all First Names using Flash Fill.
  • Task 2: Type 'Joshi' in cell C5 and press Ctrl + E to extract all Last Names.
  • Task 3: In Column E, calculate Digit Count using '=LEN(D5)'.
  • Task 4: In Column F, use an IF formula: if Digit Count = 10, return 'Valid 10-Digit', otherwise 'Error in Phone'.

Exercise Data Template Preview: Download the .xlsx file below and execute each task independently.

Full NameFirst Name (Flash Fill)Last Name (Flash Fill)Mobile NumberDigit Count (=LEN())Audit Check
Rahul Kumar Joshi9876543210
Pooja Singh Bisht981234567
Deepak Singh Rawat9012398765
Sneha Kumari Arya94111223344
Download Practice Exercise Sheet Pre-structured practice workbook with raw datasets ready for hands-on classroom lab solving.
Download Practice Sheet (.xlsx)
Mentor Pro Tip from Haldwani Classroom Lab:

Flash Fill (Ctrl + E) is one of Excel's most powerful AI features! Always review the filled data to verify Excel correctly guessed complex multi-word names.

Day 23: Text Functions — Part ... All 30 Topics Day 25: Date and Time Function...

Need 1-on-1 Guidance on Day 24?

Join our hands-on classroom lab sessions at Digital Skills Academy in Haldwani. Every student receives a dedicated PC workstation, live instructor reviews, and interview prep.