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

Day 23: Text Functions — Part 1

Manipulate strings like a pro: extract codes and join text using CONCAT, TEXTJOIN, LEFT, RIGHT, and MID.

Easy-Learn Framework

Mastering Day 23: What, Why & How

Memory Rule Included
Day 23: Text Functions — Part 1 Visual Diagram
? WHAT Is It?

Text transformation functions: =UPPER() turns text to ALL CAPS, =LOWER() to small letters, =PROPER() capitalizes the First Letter Of Each Word, =LEN() counts characters, and & combines text pieces.

! WHY Does It Matter?

Customer names and addresses imported from web forms often arrive in messy lowercase ('rahul sharma') or ALL CAPS. Text functions standardize thousands of customer records in seconds.

⚙ HOW Does It Work?
Step 1 Standardize names: =PROPER(A2) transforms 'rahul sharma' into 'Rahul Sharma'.
Step 2 Join strings: Combine First and Last Name with an ampersand: =A2 & ' ' & B2.
Step 3 Check phone numbers: =LEN(A2) counts characters (verify 10-digit mobile numbers or 12-digit Aadhaar).
Memory Hook & Golden Rule (Remember This Forever):

"Use PROPER for professional names, and the ampersand (&) with a ' ' space in between to glue text together!"

Learning Objectives

What You Will Master Today

  • =CONCAT(text1, text2) and the '&' ampersand operator to combine strings
  • =TEXTJOIN(", ", TRUE, range) to join dozens of cells with automated separators
  • =LEFT(text, 3) to extract initial characters (e.g., State codes or year prefixes)
  • =RIGHT(text, 4) to extract trailing characters (e.g., last 4 digits of phone or serials)
  • =MID(text, start_num, num_chars) to extract substring from the middle of a code
Important Keyboard Shortcuts:
=TEXTJOIN(delimiter, ignore_empty, text1, text2)=LEFT(text, num_chars)=RIGHT(text, num_chars)=MID(text, start, num_chars)
Guided Tutorial

Step-by-Step Practical Walkthrough

Step 1

To combine First Name in A5 and Last Name in B5, write '=A5 & " " & B5'.

Step 2

To extract the first 3 letters of an item code, write '=LEFT(A5, 3)'.

Step 3

To extract the last 4 digits of an invoice, write '=RIGHT(A5, 4)'.

Step 4

Use =TEXTJOIN for combining address fields with commas while skipping empty cells.

Solved Classroom Example

Haldwani Customer Directory - Address Merger & Account Number Parser

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.

Account NoFirst NameLast NameFull Name (=A5&" "&B5)Prefix (=LEFT(A5,3))Suffix (=RIGHT(A5,4))
HLD-98401RahulJoshiRahul JoshiHLD8401
RDP-76202PoojaBishtPooja BishtRDP6202
HLD-55103DeepakRawatDeepak RawatHLD5103
DDN-33404SnehaAryaSneha AryaDDN3404
NTL-12505AmitNegiAmit NegiNTL2505
Download Solved Sample Sheet Includes complete working formula cells, proper currency symbols, and clean borders.
Download Solved Sheet (.xlsx)
Student Assignment

Employee Official Email Address Generator & Code Parser

Your Turn to Practice
Exercise Tasks Checklist:
  • Task 1: In Column E, generate email address: '=LOWER(B5 & "." & C5 & "@" & D5)' (e.g., vikram.mehra@digitalskillshaldwani.in).
  • Task 2: In Column F, extract the 3-letter Branch Code from Emp ID using '=LEFT(A5, 3)'.
  • Task 3: Use '=MID(A5, 5, 4)' in a separate column to extract the joining year '2026'.
  • Task 4: Verify that formula results adjust automatically if names change.

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

Emp IDFirst NameLast NameCompany DomainOfficial Email AddressBranch Code
KUM-2026-01VikramMehradigitalskillshaldwani.in
KUM-2026-02NehaRawatdigitalskillshaldwani.in
KUM-2026-03KaranAryadigitalskillshaldwani.in
KUM-2026-04KavitaPandeydigitalskillshaldwani.in
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:

=TEXTJOIN is far superior to older CONCATENATE because it allows you to specify a delimiter once and automatically skips blank cells without extra commas!

Day 22: Data Validation and Dr... All 30 Topics Day 24: Text Functions — Part ...

Need 1-on-1 Guidance on Day 23?

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.