Protected Learning Content

AICPE Gurukul content is created for learning purposes. Printing, copying and unauthorized reuse are restricted.

AICPE Learning Hub Advanced Excel
Chapter 07
Advanced Text Functions
Previous Advanced Mathematical and Statistical Functions Next Date and Time Functions
Chapter 07 | Advanced Formulas and Functions

Advanced Text Functions

Clean, separate, standardize, search and combine text stored in Excel. This chapter develops the practical formula skills required to repair customer records, extract codes, prepare employee databases, create professional IDs and transform imported information into report-ready business data.

Start Learning Practical Assignment
Text Transformation Chapter · Eight Practical Activities Included
Learning Objectives

After This Chapter, You Will Be Able To

Transform inconsistent text into clean, meaningful and reusable information for professional Excel workbooks.

Extract Text

Use LEFT, RIGHT and MID to retrieve meaningful portions of codes, names and descriptions.

Clean Records

Remove extra spaces, hidden characters and inconsistent capitalisation from imported data.

Locate Patterns

Find separators and text positions to build formulas that adapt to changing record lengths.

Combine Fields

Create professional labels, IDs, email patterns and descriptions from multiple cells.

Text Function Roadmap

Select the Function According to the Required Transformation

Before writing a formula, identify whether the task requires extraction, cleaning, searching or combining.

ExtractLEFT, RIGHT, MID, TEXTBEFORE and TEXTAFTER
CleanTRIM, CLEAN and SUBSTITUTE
LocateFIND, SEARCH and LEN
CombineCONCAT, TEXTJOIN and ampersand

1 Understanding Text Data in Excel

Excel stores names, addresses, product codes, invoice references, phone numbers and descriptions as text. Even a number may behave as text when it contains leading zeroes, symbols or imported formatting. Text functions help convert these records into consistent information that can be searched, matched, grouped and reported accurately.

Definition: A text string is a sequence of letters, numbers, spaces or symbols treated as written information rather than a mathematical value.
Business RecordRaw ExampleRequired OutputLikely Functions
Employee codeNGP-SALES-0248Department: SALESMID, FIND, TEXTBEFORE/TEXTAFTER
Customer name  raHUL  SHARMA Rahul SharmaTRIM, PROPER
Email addressaccounts@sample.inDomain: sample.inTEXTAFTER or RIGHT + LEN + FIND
Invoice numberINV/2026/00452Serial: 00452TEXTAFTER or RIGHT

Text, Numbers and Leading Zeroes

A product code such as 00125 may lose its leading zeroes when stored as a number. If the value is an identifier rather than a quantity, store it as text or apply an appropriate custom number format. Do not perform arithmetic on codes merely because they contain digits.

Professional Tip: Decide whether a field is a measurement or an identifier. Amount, quantity and percentage fields are numeric; phone numbers, PIN codes, GST references and employee IDs are generally identifiers.

Practical Experiment 1: Audit Text Fields

Create a small dataset containing names, mobile numbers, emails, codes and addresses.

Step 1: Observe

Identify fields with extra spaces, unusual capitalisation, symbols or mixed formats.

Step 2: Classify

Mark each field as numeric measurement, date, identifier or descriptive text.

Step 3: Plan

Write the function or cleaning action required for every inconsistent field.

Learning Output: You will learn to diagnose text problems before selecting formulas.

2 Extracting Text with LEFT, RIGHT and MID

Extraction functions return selected characters from a text string. LEFT starts from the beginning, RIGHT starts from the end, and MID starts from a specified position. These functions are ideal when the code structure is fixed or when the starting position can be calculated using FIND or SEARCH.

=LEFT(A2,3)

Returns the first three characters from A2. From NGP-0248, the result is NGP.

=RIGHT(A2,4)

Returns the final four characters. From NGP-0248, the result is 0248.

=MID(A2,5,5)

Starts at character 5 and returns five characters. Useful for fixed-position department codes.

=LEN(A2)

Counts every character, including spaces, and supports dynamic extraction calculations.

Fixed-Length and Variable-Length Records

RIGHT(A2,4) works well when every serial number contains exactly four digits. If the length changes, use a separator-based method such as TEXTAFTER or calculate the required position with FIND. Professional formulas should remain correct when new records differ from the first sample.

Common risk: MID requires both a starting position and number of characters. A hard-coded formula can return incorrect results when the record format changes.

Practical Experiment 2: Separate Employee Codes

Use sample values such as NGP-SALES-0248, PUN-HR-0312 and MUM-ADMIN-0076.

Step 1: Extract City

Use LEFT to return the three-character location code.

Step 2: Extract Serial

Use RIGHT to return the final four-digit employee serial.

Step 3: Extract Department

Compare a fixed MID formula with a separator-based formula and note which is safer.

Learning Output: You will understand the difference between fixed-position and dynamic text extraction.

3 Cleaning Text with LEN, TRIM and CLEAN

Data copied from websites, PDF files, external software or online forms often contains extra spaces and hidden characters. These may not be visible, but they can cause lookup failures, duplicate-looking records and incorrect comparisons.

FunctionPurposeExampleImportant Note
LENCounts characters=LEN(A2)Useful for detecting invisible or extra characters.
TRIMRemoves leading, trailing and repeated spaces=TRIM(A2)Keeps one normal space between words.
CLEANRemoves many non-printable characters=CLEAN(A2)Often combined with TRIM for imported data.
CombinedCleans hidden characters and spacing=TRIM(CLEAN(A2))A strong first step for external text.
Before Cleaning  PRIYA   DESHMUKH 
Length may be greater than expected due to extra spaces.
After TRIMPRIYA DESHMUKH
Leading, trailing and repeated spaces are removed.
Important: TRIM removes the standard space character. Some web data contains non-breaking spaces; SUBSTITUTE may be required to replace CHAR(160) before applying TRIM.

Practical Experiment 3: Repair Imported Customer Names

Paste deliberately inconsistent customer names into a worksheet.

Step 1: Measure

Use LEN to compare character counts before cleaning.

Step 2: Clean

Apply TRIM(CLEAN(A2)) and fill the formula down.

Step 3: Validate

Recalculate LEN and check whether apparent duplicates now match correctly.

Learning Output: You will see how invisible characters influence reporting accuracy.

4 Standardizing Capitalisation with UPPER, LOWER and PROPER

Consistent text case improves the professional appearance of reports and prevents manual editing. UPPER converts text to capital letters, LOWER converts text to small letters, and PROPER capitalises the first letter of each word.

FunctionRaw ValueResultSuitable Application
=UPPER(A2)ngp-hr-0248NGP-HR-0248Codes and abbreviations
=LOWER(A2)INFO@AICPE.ONLINEinfo@aicpe.onlineEmail addresses and system usernames
=PROPER(A2)meera joshiMeera JoshiNames, cities and ordinary titles
Use judgement: PROPER may change abbreviations or special names incorrectly. For example, “AICPE INDIA” becomes “Aicpe India.” Review outputs that contain acronyms, initials or brand-specific capitalisation.

Practical Experiment 4: Standardize a Contact Database

Create separate columns for customer name, email, city and customer code.

Step 1: Apply Case

Use PROPER for names and cities, LOWER for emails and UPPER for codes.

Step 2: Review Exceptions

Identify initials, acronyms or names that require manual review.

Step 3: Finalize Values

Copy the cleaned formulas and paste them as values into a final master sheet.

Learning Output: You will build a consistent and presentable database without editing each cell manually.

6 Combining Text with Ampersand, CONCAT and TEXTJOIN

Combining fields is useful for full names, addresses, product descriptions, message templates and unique identifiers. The ampersand operator joins individual items, CONCAT joins a range or several arguments, and TEXTJOIN adds a chosen delimiter while combining values.

MethodExample FormulaBest UseKey Behaviour
Ampersand=A2&" "&B2Simple, controlled combinationsEvery separator must be added manually.
CONCAT=CONCAT(A2:C2)Joining a range directlyDoes not automatically insert separators.
TEXTJOIN=TEXTJOIN(", ",TRUE,A2:C2)Addresses and listsCan ignore empty cells and insert one delimiter.

Creating a Professional Customer ID

A formula such as =UPPER(LEFT(A2,3)&"-"&LEFT(B2,3)&"-"&TEXT(C2,"0000")) can combine city, customer name and a serial number into a standardized identifier. The TEXT function controls the display of leading zeroes in the serial portion.

Professional Tip: Use TEXTJOIN with the ignore-empty argument set to TRUE when some address components may be blank. This prevents repeated commas or unnecessary spaces.

Practical Experiment 6: Build Address Labels

Use separate columns for Address Line, Area, City, State and PIN Code.

Step 1: Combine

Use TEXTJOIN with comma and space as the delimiter.

Step 2: Ignore Blanks

Leave one optional field empty and confirm that no duplicate comma appears.

Step 3: Format

Use PROPER or UPPER selectively to create a professional mailing label.

Learning Output: You will create flexible combined outputs that remain clean when fields are missing.

7 Modern Extraction with TEXTBEFORE and TEXTAFTER

In current versions of Excel, TEXTBEFORE and TEXTAFTER provide simpler separator-based extraction. Instead of calculating character positions manually, you specify the delimiter and optionally choose which occurrence should be used.

=TEXTBEFORE(A2,"-")

Returns all text before the first hyphen.

=TEXTAFTER(A2,"-",-1)

Returns all text after the final hyphen by counting from the end.

=TEXTBEFORE(A2,"-",2)

Returns everything before the second hyphen.

=TEXTAFTER(A2,"@")

Returns the domain portion of an email address.

Compatibility note: TEXTBEFORE and TEXTAFTER may not be available in older Excel versions. When workbook compatibility is important, maintain an alternative using LEFT, RIGHT, MID, LEN and FIND.

Practical Experiment 7: Compare Old and Modern Formulas

Extract the domain from an email and the serial number from an invoice reference using two methods.

Step 1: Traditional

Build the result with RIGHT, LEN and FIND.

Step 2: Modern

Create the same result using TEXTAFTER.

Step 3: Evaluate

Compare readability, compatibility and ease of maintenance.

Learning Output: You will select formulas based on both simplicity and the Excel version used by the organization.

8 Building Reliable Text Transformation Formulas

Real-world formulas often combine several text functions. A customer name may need CLEAN, TRIM and PROPER. A code may require TEXTAFTER, UPPER and SUBSTITUTE. Build formulas in small tested stages before combining them into a final expression.

1

Inspect

Identify separators, inconsistent spaces, text case and variable lengths.

2

Test Parts

Verify each extraction or cleaning function in a helper column.

3

Combine

Nest only the functions required to produce the final output.

4

Validate

Test unusual records, blanks, missing delimiters and older Excel compatibility.

=PROPER(TRIM(CLEAN(A2)))

Cleans hidden characters, removes excess spaces and applies professional name case.

=UPPER(SUBSTITUTE(TRIM(B2)," ","-"))

Creates a standardized hyphenated code from inconsistent text.

Practical Experiment 8: Create a Data-Cleaning Pipeline

Use a raw customer export containing inconsistent names, codes, emails and addresses.

Step 1: Helper Columns

Create separate columns for clean name, email domain, customer code and complete address.

Step 2: Validate

Check blank records, missing separators and unexpected character lengths.

Step 3: Final Master

Copy reviewed outputs and paste values into a protected master-data sheet.

Learning Output: You will transform raw information through a repeatable, auditable workflow.
Real-Time Practical Assignment

Customer Master Data Cleaning and ID Builder

Convert an inconsistent customer export into a clean, searchable and professionally structured master database.

Assignment: Customer Data Transformation System

Prepare at least 75 records with Raw Name, Mobile, Email, Address Line, City, State, PIN Code, Product Code and Invoice Reference.

1

Prepare Raw Data

Add realistic errors such as repeated spaces, mixed case, inconsistent separators and blank address fields.

2

Clean Records

Use CLEAN, TRIM, UPPER, LOWER and PROPER according to the type of field.

3

Extract and Combine

Extract email domains and invoice serials, then create a customer ID and complete address.

4

Review Exceptions

Identify missing delimiters, invalid emails and names requiring manual capitalisation correction.

Required Output: A workbook containing Raw Import, Cleaning Workspace, Exception Report and Final Customer Master worksheets.

Assignment Checklist

1
Raw data is preserved separately.The original imported information remains unchanged for comparison.
2
Text case matches field purpose.Names and cities are readable, emails are lowercase and codes are standardized.
3
Extraction works for variable lengths.Formulas are tested with short and long domains, names and references.
4
Blank fields are handled cleanly.Combined addresses do not contain repeated commas or unnecessary spaces.
5
Exceptions are documented.Records requiring manual review are separated instead of silently corrected.

Text Formula Selection Lab

Select the required task to receive a suitable function recommendation and sample formula.

Recommended: =LEFT(A2,3)
Common Mistakes

Mistakes Students and Professionals Should Avoid

Text formulas often look correct for one sample but fail when record length, spacing or separators change.

Wrong Habits

  • Using fixed MID positions without checking whether code lengths vary.
  • Applying PROPER blindly to acronyms, initials and brand names.
  • Ignoring hidden spaces when lookup results fail.
  • Combining fields without separators or blank-field control.
  • Using SEARCH when a case-sensitive match is required.
  • Replacing the raw import instead of preserving an original copy.
  • Using modern functions without checking the recipient’s Excel version.

Correct Habits

  • Inspect several records before designing the extraction formula.
  • Use separator positions for variable-length codes and references.
  • Combine CLEAN and TRIM for text imported from external sources.
  • Review exceptions after applying automated capitalisation.
  • Use TEXTJOIN with ignore-empty enabled for optional address fields.
  • Test formulas with blanks, missing separators and unusual characters.
  • Document whether modern or legacy-compatible formulas were used.
Remember: Text cleaning is not only cosmetic. Consistent text improves lookups, duplicate checks, filtering, grouping and the reliability of every report built from the data.
AICPE Quality Learning Commitment

AICPE Gurukul is designed to provide practical, skill-based and career-oriented learning content for students, institutes and professionals. Learn more at aicpeindia.org and aicpe.online.

Knowledge Check

Quick Quiz: Advanced Text Functions

Answer all 12 questions, submit the quiz and review the explanations.

1. Which function returns characters from the beginning of a text string?

LEFT returns the specified number of characters starting from the beginning of a text string.

2. What information does MID require?

MID needs the source text, starting position and number of characters to return.

3. Which formula is commonly used to remove hidden characters and extra spaces?

CLEAN removes many non-printable characters and TRIM removes unnecessary standard spaces.

4. Which function converts an email address to small letters?

LOWER converts all letters in the text to lowercase.

5. What is the main difference between FIND and SEARCH?

FIND distinguishes uppercase and lowercase letters, while SEARCH normally does not.

6. Which function can replace only a selected occurrence of the same symbol?

SUBSTITUTE includes an optional instance number that can target a particular occurrence.

7. Which function can combine a range while inserting one chosen delimiter and ignoring blank cells?

TEXTJOIN accepts a delimiter and an ignore-empty argument before the text items.

8. Which modern function directly returns text after the @ symbol?

TEXTAFTER returns the portion of text appearing after a specified delimiter.

9. Why is LEN useful during data cleaning?

LEN counts all characters, including spaces, helping reveal values that are longer than expected.

10. What risk should be considered before using PROPER?

PROPER capitalises each word and can incorrectly alter abbreviations, initials or special brand names.

11. Which approach is safest for extracting a variable-length department between two hyphens?

Separator-based logic adapts better when the department name contains different numbers of characters.

12. Why should the original imported data be preserved?

Keeping the source data unchanged allows users to verify transformations and recover from incorrect cleaning rules.
Quick Revision

Remember These Essential Points

Review the transformation principles before moving to date and time functions.

Extraction

LEFT, RIGHT and MID extract characters; separator-based formulas are safer for variable-length records.

Cleaning

TRIM and CLEAN remove spacing and non-printable character problems from imported data.

Capitalisation

Use UPPER, LOWER and PROPER according to field purpose, then review exceptions.

Location

FIND is case-sensitive, while SEARCH is generally case-insensitive.

Combining

TEXTJOIN creates clean combined outputs with a delimiter and optional blank-cell control.

Compatibility

TEXTBEFORE and TEXTAFTER are simpler, but older Excel versions may require traditional alternatives.

Previous: Mathematical and Statistical Functions Next: Date and Time Functions