Advanced Text Formula Excel Practice Questions with Solutions

Introduction

Advanced text formulas become useful when Excel data is not stored in a simple, clean format. In real projects, you may need to extract information from product codes, clean customer records, create dynamic IDs, split text, combine multiple values, or handle different text patterns. This chapter combines multiple Excel text functions into practical problems so you can test your ability to build formulas instead of using only one function at a time. Advanced Text Formula Excel Practice questions with solutions to help you understand the concepts.

Question 1: Extract Username and Domain from Email

Problem Statement

You have a list of email addresses. Extract both the username and the domain into separate columns.

Excel Data

Solution

In B2, enter:

=TEXTBEFORE(A2,"@")

In C2, enter:

=TEXTAFTER(A2,"@")

Copy both formulas down.

Expected Output

Question 2: Create a Standardized Employee ID

Problem Statement

Create an employee ID using:

• First letter of employee name
• Department code
• Employee number with three digits

Required format:

R-IT-001

Excel Data

Solution

In D2, enter:

=UPPER(LEFT(TRIM(A2),1))&"-"&UPPER(LEFT(TRIM(B2),2))&"-"&TEXT(C2,"000")

Copy down.

Expected Output

This combines cleaning, extraction, capitalization, and number formatting.

Question 3: Extract Product Details from a Structured Code

Problem Statement

Product codes follow this format:

ELEC-LAP-2026-101

Extract:

• Category
• Product type
• Year
• Product number

Excel Data

Solution

In B2, enter:

=TEXTBEFORE(A2,"-")

In C2, enter:

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

In D2, enter:

=TEXTBEFORE(TEXTAFTER(A2,"-",2),"-")

In E2, enter:

=TEXTAFTER(A2,"-",3)

Copy the formulas down.

Expected Output

Question 4: Clean and Standardize Customer Names

Problem Statement

Customer names contain extra spaces and inconsistent capitalization. Clean them and convert them to proper case.

Excel Data

Solution

In B2, enter:

=PROPER(TRIM(CLEAN(A2)))

Copy down.

Expected Output

Question 5: Extract the Initials from a Full Name

Problem Statement

Create initials from each employee’s first and last name.

For example:

Rahul Sharma → RS

Excel Data

Solution

In B2, enter:

=UPPER(LEFT(A2,1)&TEXTAFTER(A2," ",-1,1))

For this task, a simpler and more reliable formula is:

=UPPER(LEFT(A2,1)&LEFT(TEXTAFTER(A2," "),1))

Copy down.

Expected Output

Question 6: Replace Multiple Unwanted Characters

Problem Statement

Phone numbers have been imported with different separators:

• -
• .
• spaces
• /

Create a clean phone number containing digits only.

Excel Data

Solution

In B2, enter:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-",""),".","")," ",""),"/","")

Copy down.

Expected Output

Question 7: Create a Customer Summary from Multiple Columns

Problem Statement

Create one readable customer summary containing:

• Customer name
• City
• Membership
• Email

Skip any blank values.

Excel Data

Solution

In E2, enter:

=TEXTJOIN(" | ",TRUE,A2:D2)

Copy down.

Expected Output

TEXTJOIN ignores the blank email in Neha’s record.

Question 8: Extract Text Between Two Delimiters

Problem Statement

Product information is stored in this format:

Product: Laptop | Category: Electronics | City: Delhi

Extract only the category.

Excel Data

Solution

In B2, enter:

=TEXTBEFORE(TEXTAFTER(A2,"Category: "), " |")

Copy down.

Expected Output

This is a useful pattern when data contains several labeled pieces of information in one cell.

Question 9: Create a Dynamic Email Address

Problem Statement

Create a company email address using the employee’s first name and last name.

Required format:

rahul.sharma@company.com

The names may contain extra spaces or inconsistent capitalization.

Excel Data

Solution

In C2, enter:

=LOWER(TRIM(A2)&"."&TRIM(B2)&"@company.com")

Copy down.

Expected Output

Question 10: Complete Advanced Text Cleaning Project

Problem Statement

A company receives customer information in one column in the following format:

RAHUL SHARMA | rahul@gmail.com | DELHI | CUST-001

The data contains extra spaces and inconsistent capitalization.

Create four separate clean columns:

1. Customer Name
2. Email
3. City
4. Customer ID

Excel Data

Solution

Customer Name

In B2, enter:

=PROPER(TRIM(TEXTBEFORE(A2,"|")))

Email

In C2, enter:

=LOWER(TRIM(TEXTBEFORE(TEXTAFTER(A2,"|"),"|")))

City

In D2, enter:

=PROPER(TRIM(TEXTBEFORE(TEXTAFTER(A2,"|",2),"|")))

Customer ID

In E2, enter:

=TRIM(TEXTAFTER(A2,"|",3))

Copy all formulas down.

Expected Output

This exercise combines TEXTBEFORE, TEXTAFTER, TRIM, PROPER, and LOWER into a practical data-cleaning workflow.

Key Takeaways

• Advanced text formulas usually combine two or more Excel functions.
• TEXTBEFORE and TEXTAFTER are useful for extracting information around delimiters.
• TEXTSPLIT can split structured text into multiple cells.
• TRIM is useful for removing unnecessary spaces.
• CLEAN helps remove non-printable characters from imported data.
• PROPER, UPPER, and LOWER help standardize text.
• SUBSTITUTE can remove multiple unwanted characters when nested.
• TEXTJOIN can combine multiple values while ignoring blank cells.
• TEXT is useful for creating formatted numbers and IDs.
• FIND and SEARCH can help create dynamic extraction formulas.
• Complex text formulas are particularly useful for cleaning imported CSV, website, CRM, and database data.
• The main skill in advanced text formulas is understanding how individual functions can work together.

FAQs

1. What are advanced text formulas in Excel?

Advanced text formulas are formulas that combine multiple text functions to solve more complex problems such as extracting, cleaning, splitting, standardizing, and rebuilding text data.

2. Why should I combine multiple Excel text functions?

One function may not be enough for real-world data. For example, you may need to extract text and then remove spaces or change capitalization.

=PROPER(TRIM(TEXTBEFORE(A2,"|")))

This single formula extracts text, removes unnecessary spaces, and formats the result.

3. What is the difference between TEXTBEFORE and TEXTAFTER?

TEXTBEFORE returns the text before a specified delimiter.

=TEXTBEFORE(A2,"@")

TEXTAFTER returns the text after the delimiter.

=TEXTAFTER(A2,"@")

4. How can I clean imported customer data?

A common approach is to combine functions such as:

TRIM
CLEAN
PROPER
LOWER
SUBSTITUTE
TEXTBEFORE
TEXTAFTER

The exact combination depends on how the source data is structured.

5. How can I remove multiple unwanted characters?

You can nest SUBSTITUTE functions.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-",""),".","")," ","")

This can remove multiple types of unwanted separators.

6. How can I create an email address from first and last names?

For example:

=LOWER(TRIM(A2)&"."&TRIM(B2)&"@company.com")

This combines the names, removes extra spaces, and converts the result to lowercase.

7. Can TEXTJOIN ignore blank cells?

Yes. Set the second argument to TRUE.

=TEXTJOIN(", ",TRUE,A2:D2)

Blank cells in the range are skipped.

8. Can TEXTSPLIT be used to separate names?

Yes. If a full name contains spaces:

=TEXTSPLIT(A2," ")

Excel can split the name across multiple cells.

9. Why is TRIM useful with TEXTBEFORE and TEXTAFTER?

Imported data often contains spaces around delimiters. For example:

Rahul Sharma | Delhi

Using:

=TRIM(TEXTBEFORE(A2,"|"))

removes the unwanted space after the extracted text.

10. What should I learn before practicing advanced text formulas?

You should be comfortable with basic functions such as LEFT, RIGHT, MID, LEN, FIND, SEARCH, TRIM, CLEAN, SUBSTITUTE, CONCAT, and TEXTJOIN. Advanced formulas are mainly about combining these functions effectively.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar