Top 50 Microsoft Excel Text Formulas with Examples (Beginner to Advanced)

Master the most useful Microsoft Excel Text Formulas with real-world examples. Learn how to extract, combine, clean, search, split, and format text like a professional using step-by-step explanations.

✔ 50 Text Formulas ✔ Beginner to Advanced ✔ Practical Examples ✔ Interview Ready

📘 What You Will Learn

Extract Text from Cells
Join Multiple Text Values
Search Specific Characters
Replace Words
Split Full Names
Clean Imported Data
Convert Text to Numbers
Professional Office Examples
Interview Questions
Real-Life Projects

📚 Top 50 Excel Text Formulas List

No.	Formula	Purpose
1	LEFT()	Extract Left Characters
2	RIGHT()	Extract Right Characters
3	MID()	Extract Middle Characters
4	LEN()	Count Characters
5	TRIM()	Remove Extra Spaces
6	UPPER()	Uppercase Text
7	LOWER()	Lowercase Text
8	PROPER()	Capitalize Words
9	CONCAT()	Join Text
10	TEXTJOIN()	Join with Separator
11	TEXT()	Format Numbers
12	FIND()	Find Position
13	SEARCH()	Search Text
14	REPLACE()	Replace Characters
15	SUBSTITUTE()	Replace Words
16	EXACT()	Compare Text
17	REPT()	Repeat Text
18	CLEAN()	Remove Non-printable Characters
19	CHAR()	Insert Characters
20	CODE()	ASCII Code

🚀 Let's Start Learning

Each formula in this guide includes:

✅ Real Excel Sheet Example
✅ Row & Column Table
✅ Formula Syntax
✅ Formula Breakdown
✅ Student Example
✅ Office Example
✅ Common Mistakes
✅ Practice Exercise
✅ Pro Tips

Let's begin with Formula 1: LEFT()

🔤 Formula 1 : LEFT()

The LEFT() function returns a specified number of characters from the left side of a text string. It is commonly used to extract state codes, product codes, employee IDs, invoice prefixes, and student roll numbers.

📌 Student Roll Number Example

	A	B
1	Roll Number	State Code
2	GJ2026001	=LEFT(A2,2)
3	MH2026002
4	RJ2026003

📝 Formula

=LEFT(A2,2)

📖 Formula Breakdown

Argument	Meaning
A2	Original Text
2	Number of Characters from Left

Result

GJ2026001

⬇

GJ

📌 More LEFT() Examples

Formula	Output
=LEFT("Microsoft",5)	Micro
=LEFT("Excel",2)	Ex
=LEFT("INV-2026-001",3)	INV

💼 Real-Life Uses

Extract State Codes.
Get Product Prefix.
Read Invoice Numbers.
Separate Employee Codes.
Extract Country Codes.
Data Cleaning.

❌ Common Mistakes

Using more characters than available.
Using LEFT() for numbers without understanding formatting.
Forgetting quotation marks when using direct text.

📝 Practice Exercise

Extract the first 3 letters from "Computer".
Extract the first 2 digits from a student ID.
Extract "EMP" from EMP1025.

⭐ LEFT() vs RIGHT()

Function	Purpose
LEFT()	Extract Characters from Left
RIGHT()	Extract Characters from Right

💡 Pro Tip

Combine LEFT() with FIND(), SEARCH(), LEN(), and MID() to split names, extract codes, clean imported data, and build professional Excel reports.

➡️ Formula 2 : RIGHT()

The RIGHT() function returns a specified number of characters from the right side of a text string. It is commonly used to extract invoice numbers, mobile number endings, employee IDs, product codes, and student roll numbers.

📌 Student Roll Number Example

	A	B
1	Roll Number	Student ID
2	GJ2026001	=RIGHT(A2,3)
3	MH2026045
4	RJ2026125

📝 Formula

=RIGHT(A2,3)

📖 Formula Breakdown

Argument	Meaning
A2	Original Text
3	Characters from Right Side

Result

GJ2026001

⬇

001

📌 More RIGHT() Examples

Formula	Output
=RIGHT("Microsoft",5)	osoft
=RIGHT("9876543210",4)	3210
=RIGHT("INV-2026-001",3)	001

💼 Real-Life Uses

Extract Invoice Numbers.
Get Last 4 Digits of Mobile Numbers.
Extract Employee IDs.
Read Product Codes.
Separate Roll Numbers.
Data Cleaning & Validation.

❌ Common Mistakes

Using more characters than available.
Confusing RIGHT() with LEFT().
Using numbers without proper formatting.

📝 Practice Exercise

Extract the last 3 letters from "Computer".
Extract the last 4 digits from a mobile number.
Extract "125" from RJ2026125.

⭐ LEFT() vs RIGHT()

Function	Purpose
LEFT()	Extract Characters from Left
RIGHT()	Extract Characters from Right

💡 Pro Tip

Combine RIGHT() with LEN(), SEARCH(), FIND(), and MID() to extract invoice numbers, mobile numbers, employee IDs, and other important information automatically in Excel.

🔠 Formula 3 : MID()

MID() returns a specific number of characters from the middle of a text string. It is useful for extracting product codes, student IDs, PAN numbers, Aadhaar digits, and invoice details.

📌 Example

	A	B
1	Student ID	Year
2	GJ2026001	=MID(A2,3,4)

📝 Formula

=MID(A2,3,4)

📖 Result

2026

📌 Breakdown

A2 → Original Text
3 → Starting Position
4 → Number of Characters

💼 Uses

Extract Year
Extract Product Code
Extract PAN Characters
Extract Invoice Number
Extract Employee ID

❌ Common Mistakes

Wrong Starting Position.
Using more characters than available.
Confusing MID() with LEFT() or RIGHT().

💡 Pro Tip: Combine MID() with SEARCH(), FIND(), and LEN() to extract dynamic text.

📏 Formula 4 : LEN()

LEN() returns the total number of characters in a text string, including spaces. It is useful for data validation, password length checks, and text analysis.

📌 Example

	A	B
1	Name	Length
2	Microsoft	=LEN(A2)

📝 Formula

=LEN(A2)

📖 Result

📌 Breakdown

A2 → Text to Count

💼 Uses

Count Characters
Password Validation
Check Mobile Number Length
Validate Student IDs
Clean Imported Data

❌ Common Mistakes

LEN() counts spaces.
Numbers stored as text are also counted.
Extra spaces increase the character count.

💡 Pro Tip: Use LEN(TRIM(A2)) to count characters after removing extra spaces.

🧹 Formula 5 : TRIM()

TRIM() removes extra spaces from text while keeping a single space between words. It is one of the most useful functions for cleaning imported data, employee lists, student names, and customer databases.

📌 Student Name Example

	A	B
1	Original Name	Clean Name
2	Rahul Patel	=TRIM(A2)

📝 Formula

=TRIM(A2)

📖 Result

Rahul Patel

📌 Breakdown

A2 → Text containing extra spaces
TRIM() removes unnecessary spaces automatically.

💼 Real-Life Uses

Clean Imported Data
Remove Extra Spaces
Prepare Data for VLOOKUP()
Student Records
Employee Database
Customer Lists

❌ Common Mistakes

TRIM() removes only normal spaces.
It does not remove non-breaking spaces from web data.
Use CLEAN() together with TRIM() for imported files.

💡 Pro Tip: Use =TRIM(CLEAN(A2)) to clean imported Excel or website data before using lookup formulas.

🔠 Formula 6 : UPPER()

UPPER() converts all letters in a text string into uppercase letters. It is useful for employee IDs, product codes, GST numbers, PAN numbers, and standardizing text.

📌 Employee Name Example

	A	B
1	Name	Uppercase
2	Rahul Patel	=UPPER(A2)

📝 Formula

=UPPER(A2)

📖 Result

RAHUL PATEL

📌 Breakdown

A2 → Original Text
UPPER() converts every letter to CAPITAL LETTERS.

💼 Real-Life Uses

Employee IDs
GST Numbers
PAN Numbers
Product Codes
Database Standardization
Official Reports

❌ Common Mistakes

Numbers remain unchanged.
Special symbols are not affected.
Only alphabetic characters become uppercase.

💡 Pro Tip: Combine UPPER() with TRIM(), LOWER(), and PROPER() to standardize names and imported datasets for professional Excel reports.

🔡 Formula 7 : LOWER()

LOWER() converts all uppercase letters in a text string into lowercase letters. It is commonly used for email addresses, usernames, website URLs, and database standardization.

📌 Email Example

	A	B
1	Email	Lowercase
2	RAHUL@GMAIL.COM	=LOWER(A2)

📝 Formula

=LOWER(A2)

📖 Result

rahul@gmail.com

💼 Real-Life Uses

Convert Email IDs
Create Website URLs
Database Cleaning
Username Formatting
Standardize Text

💡 Pro Tip: Use LOWER(TRIM(A2)) before importing customer or employee data.

📝 Formula 8 : PROPER()

PROPER() converts the first letter of every word into uppercase while converting the remaining letters into lowercase. It is ideal for names, addresses, cities, and company names.

📌 Student Name Example

	A	B
1	Original Name	Formatted Name
2	rAHUL pATEL	=PROPER(A2)

📝 Formula

=PROPER(A2)

📖 Result

Rahul Patel

💼 Real-Life Uses

Student Lists
Employee Records
Customer Database
Address Formatting
Official Reports

💡 Pro Tip: Combine TRIM() + PROPER() to automatically clean and format names.

🔗 Formula 9 : CONCAT()

CONCAT() joins two or more text strings into one text value. It replaces the old CONCATENATE() function in modern versions of Microsoft Excel.

📌 Student Full Name Example

	A	B	C
1	First Name	Last Name	Full Name
2	Rahul	Patel	=CONCAT(A2," ",B2)

📝 Formula

=CONCAT(A2," ",B2)

📖 Result

Rahul Patel

💼 Real-Life Uses

Create Full Names
Generate Employee IDs
Merge Addresses
Create Product Codes
Prepare Mailing Lists
Build Reports

❌ Common Mistakes

Forgetting to add spaces between words.
Using CONCAT() instead of TEXTJOIN() when separators are needed.
Mixing numbers and text without formatting.

💡 Pro Tip: Use =CONCAT(A2," ",B2," - ",C2) to combine multiple values into one professional text string.

🔗 Formula 10 : TEXTJOIN()

TEXTJOIN() combines multiple text values into one cell using a specified separator. It is much more powerful than CONCAT() because it can automatically ignore empty cells.

📌 Student Full Address Example

	A	B	C	D
1	City	State	Country	Full Address
2	Patan	Gujarat	India	=TEXTJOIN(", ",TRUE,A2:C2)

📝 Formula

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

📖 Result

Patan, Gujarat, India

💼 Real-Life Uses

Create Full Addresses
Merge Customer Details
Generate Reports
Create Product Descriptions
Combine Multiple Cells

💡 Pro Tip: TEXTJOIN() automatically ignores blank cells when the second argument is TRUE.

💲 Formula 11 : TEXT()

TEXT() converts numbers, dates, and times into text using a custom format. It is useful for reports, invoices, dashboards, and professional formatting.

📌 Salary Example

	A	B
1	Salary	Formatted
2	25000	=TEXT(A2,"₹ #,##0")

📝 Formula

=TEXT(A2,"₹ #,##0")

📖 Result

₹ 25,000

💼 Real-Life Uses

Currency Formatting
Date Formatting
Invoice Design
Dashboard Reports
Professional Print Reports

💡 Pro Tip: Combine TEXT() with TODAY() or NOW() to create beautifully formatted dates and times.

🔍 Formula 12 : FIND()

FIND() returns the position of a specific character or word inside another text string. FIND() is case-sensitive and is widely used with LEFT(), MID(), and RIGHT().

📌 Email Example

	A	B
1	Email	@ Position
2	rahul@gmail.com	=FIND("@",A2)

📝 Formula

=FIND("@",A2)

📖 Result

💼 Real-Life Uses

Find @ in Email IDs
Extract Domain Names
Split Full Names
Locate Product Codes
Extract File Extensions

❌ Common Mistakes

FIND() is case-sensitive.
If the text is not found, it returns #VALUE!.
Use SEARCH() when case sensitivity is not required.

💡 Pro Tip: Combine FIND() with LEFT(), MID(), and RIGHT() to extract names, email domains, and product codes automatically.

🔎 Formula 13 : SEARCH()

SEARCH() returns the position of a character or text within another text string. Unlike FIND(), SEARCH() is not case-sensitive, making it ideal for flexible text searches.

📌 Email Example

	A	B
1	Email Address	@ Position
2	Rahul@gmail.com	=SEARCH("@",A2)

📝 Formula

=SEARCH("@",A2)

📖 Result

💼 Real-Life Uses

Find Email Symbols
Locate Product Codes
Search Keywords
Extract Website Domains
Split Customer Data

❌ Common Mistakes

SEARCH() is not case-sensitive.
Returns #VALUE! if text is not found.
Do not confuse SEARCH() with FIND().

💡 Pro Tip: Use SEARCH() with MID(), LEFT(), and RIGHT() to dynamically extract text from long strings.

✏️ Formula 14 : REPLACE()

REPLACE() replaces characters in a text string based on their position. It is useful for correcting codes, masking numbers, and editing fixed-length text.

📌 Mobile Number Example

	A	B
1	Mobile Number	Masked Number
2	9876543210	=REPLACE(A2,4,4,"****")

📝 Formula

=REPLACE(A2,4,4,"****")

📖 Result

987****210

💼 Real-Life Uses

Mask Mobile Numbers
Edit Employee Codes
Replace Invoice Digits
Correct Product Codes
Data Security

❌ Common Mistakes

REPLACE() works by character position, not by matching words.
Incorrect start position changes unexpected characters.

💡 Pro Tip: Use REPLACE() to hide confidential information such as Aadhaar numbers, phone numbers, and account numbers.

🔄 Formula 15 : SUBSTITUTE()

SUBSTITUTE() replaces one text value with another by matching the actual text instead of the character position. It is perfect for replacing words, symbols, and repeated characters.

📌 Product Example

	A	B
1	Product	Updated Product
2	Laptop Old Model	=SUBSTITUTE(A2,"Old","New")

📝 Formula

=SUBSTITUTE(A2,"Old","New")

📖 Result

Laptop New Model

💼 Real-Life Uses

Replace Product Names
Update Company Names
Remove Symbols
Replace Spaces with Hyphens
Website URL Formatting
Data Cleaning

❌ Common Mistakes

SUBSTITUTE() is case-sensitive.
It replaces matching text, not character positions.
Incorrect spelling prevents replacement.

💡 Pro Tip: Combine SUBSTITUTE() with TRIM(), SEARCH(), and TEXTJOIN() to clean imported data and build professional Excel reports automatically.

✅ Formula 16 : EXACT()

EXACT() compares two text strings and returns TRUE if they are exactly the same, including uppercase and lowercase letters. It is a case-sensitive function.

📌 Username Example

	A	B	C
1	Username 1	Username 2	Result
2	Rahul	RAHUL	=EXACT(A2,B2)

📝 Formula

=EXACT(A2,B2)

📖 Result

FALSE

💼 Real-Life Uses

Password Validation
Username Verification
Compare Product Codes
Verify Employee IDs
Check Imported Data

❌ Common Mistakes

EXACT() is case-sensitive.
Extra spaces affect the result.
Use TRIM() before EXACT() if data contains unwanted spaces.

💡 Pro Tip: Combine EXACT() with IF() to display "Matched" or "Not Matched" automatically.

🔁 Formula 17 : REPT()

REPT() repeats a text string a specified number of times. It is useful for creating visual progress bars, repeating symbols, and formatting reports.

📌 Rating Example

	A	B
1	Rating	Stars
2	5	=REPT("⭐",A2)

📝 Formula

=REPT("⭐",A2)

📖 Result

⭐⭐⭐⭐⭐

💼 Real-Life Uses

Star Ratings
Progress Bars
Dashboards
Visual Reports
Performance Indicators

❌ Common Mistakes

Very large repeat numbers create extremely long text.
Text length is limited by Excel's cell limit.

💡 Pro Tip: Use =REPT("█",A2) to create professional dashboard progress bars.

🧹 Formula 18 : CLEAN()

CLEAN() removes non-printable characters from text imported from websites, PDFs, databases, and external systems. It is essential for data cleaning before analysis.

📌 Imported Data Example

	A	B
1	Imported Text	Clean Text
2	Rahul Patel	=CLEAN(A2)

📝 Formula

=CLEAN(A2)

📖 Result

Rahul Patel (without hidden non-printable characters)

💼 Real-Life Uses

Clean Website Data
Import CSV Files
Clean PDF Data
Prepare Data for VLOOKUP()
Database Cleaning
Data Analysis

❌ Common Mistakes

CLEAN() does not remove normal extra spaces.
Use TRIM() together with CLEAN() for the best results.
Hidden Unicode characters may still require SUBSTITUTE().

💡 Pro Tip: Use the powerful combination:

=TRIM(CLEAN(A2))

This removes hidden characters and extra spaces, making imported Excel data ready for lookups, reports, and dashboards.

🔣 Formula 19 : CHAR()

CHAR() returns a character based on its ASCII (ANSI) code. It is commonly used to insert line breaks, symbols, special characters, and formatting characters into Excel text.

📌 Character Example

	A	B
1	ASCII Code	Character
2	65	=CHAR(A2)

📝 Formula

=CHAR(A2)

📖 Result

📌 More CHAR() Examples

Formula	Result
=CHAR(65)	A
=CHAR(97)	a
=CHAR(10)	Line Break

💼 Real-Life Uses

Insert Line Breaks
Create Special Symbols
Generate Letters
Format Reports
Create Dynamic Text

💡 Pro Tip: Use =A2&CHAR(10)&B2 to display two values on separate lines inside one cell.

🔢 Formula 20 : CODE()

CODE() returns the ASCII code of the first character in a text string. It is useful for identifying hidden characters, validating imported data, and debugging text issues.

📌 Example

	A	B
1	Character	ASCII Code
2	A	=CODE(A2)

📝 Formula

=CODE(A2)

📖 Result

📌 More CODE() Examples

Formula	Result
=CODE("A")	65
=CODE("a")	97
=CODE("1")	49

💼 Real-Life Uses

Detect Hidden Characters
Validate Imported Files
Data Cleaning
ASCII Conversion
Debug Excel Text

💡 Pro Tip: Use CODE() together with CHAR() to convert between characters and ASCII values.

💯 Formula 21 : VALUE()

VALUE() converts text that looks like a number into a real numeric value. This is useful when numbers are imported as text from websites, PDFs, or CSV files.

📌 Sales Example

	A	B
1	Text Number	Converted Number
2	'25000	=VALUE(A2)

📝 Formula

=VALUE(A2)

📖 Result

25000 (Numeric Value)

📌 More VALUE() Examples

Formula	Result
=VALUE("150")	150
=VALUE("25.75")	25.75
=VALUE("01/01/2026")	Date Serial Number

💼 Real-Life Uses

Convert Text to Numbers
Import CSV Data
Fix Calculation Errors
Prepare Financial Reports
Clean Web Data
Sales Analysis

❌ Common Mistakes

VALUE() returns #VALUE! if the text cannot be converted.
Currency symbols may require SUBSTITUTE() first.
Incorrect date formats may not convert properly.

💡 Pro Tip: Use =VALUE(SUBSTITUTE(A2,",","")) to convert text numbers containing commas into usable numeric values for calculations.

💵 Formula 22 : DOLLAR()

DOLLAR() converts a number into text formatted as currency. It is useful for invoices, salary reports, accounting statements, financial dashboards, and business reports.

📌 Salary Example

	A	B
1	Salary	Formatted Currency
2	25000	=DOLLAR(A2,2)

📝 Formula

=DOLLAR(A2,2)

📖 Result

$25,000.00

📌 Formula Breakdown

A2 → Number
2 → Decimal Places

💼 Real-Life Uses

Salary Reports
Invoices
Financial Statements
Business Dashboards
Accounting Reports

💡 Pro Tip: Use DOLLAR() when exporting reports that require professional currency formatting.

🔢 Formula 23 : FIXED()

FIXED() formats a number with a fixed number of decimal places. It can also remove commas if required.

📌 Sales Example

	A	B
1	Sales	Formatted
2	98765.456	=FIXED(A2,2)

📝 Formula

=FIXED(A2,2)

📖 Result

98,765.46

📌 More Examples

Formula	Result
=FIXED(1254.567,1)	1,254.6
=FIXED(1254.567,0)	1,255
=FIXED(1254.567,2,TRUE)	1254.57

💼 Real-Life Uses

Sales Reports
Financial Statements
Dashboard Numbers
Printed Reports
Accounting

💡 Pro Tip: Use TRUE in the third argument to remove comma separators.

🌍 Formula 24 : NUMBERVALUE()

NUMBERVALUE() converts text into a numeric value using custom decimal and thousands separators. It is very useful when importing international financial data.

📌 International Number Example

	A	B
1	Text Number	Converted Value
2	25.500,75	=NUMBERVALUE(A2,",",".")

📝 Formula

=NUMBERVALUE(A2,",",".")

📖 Result

25500.75

📌 Formula Breakdown

A2 → Text Number
"," → Decimal Separator
"." → Thousands Separator

💼 Real-Life Uses

International Accounting
Financial Data Import
CSV Files
ERP Reports
Business Analytics
Global Sales Reports

❌ Common Mistakes

Using the wrong decimal separator.
Using the wrong thousands separator.
Incorrect regional formats return errors.

💡 Pro Tip: NUMBERVALUE() is one of the best formulas for handling international Excel files where number formats differ between countries.

🌐 Formula 25 : UNICHAR()

UNICHAR() returns a Unicode character based on its Unicode number. It is useful for displaying symbols, emojis, arrows, check marks, stars, currency symbols, and multilingual characters.

📌 Symbol Example

	A	B
1	Unicode	Character
2	9733	=UNICHAR(A2)

📝 Formula

=UNICHAR(A2)

📖 Result

★

📌 More Examples

Formula	Result
=UNICHAR(10003)	✓
=UNICHAR(9733)	★
=UNICHAR(8594)	→

💼 Real-Life Uses

Dashboard Icons
Star Ratings
Check Marks
Reports
Interactive Excel Sheets

💡 Pro Tip: Use UNICHAR() with Conditional Formatting to create beautiful KPI dashboards.

🔢 Formula 26 : UNICODE()

UNICODE() returns the Unicode number of the first character in a text string. It is useful for validating Unicode characters and multilingual text.

📌 Example

	A	B
1	Character	Unicode
2	★	=UNICODE(A2)

📝 Formula

=UNICODE(A2)

📖 Result

9733

💼 Real-Life Uses

Unicode Validation
Language Processing
Special Character Detection
Data Cleaning
International Reports

💡 Pro Tip: Use UNICODE() together with UNICHAR() to convert between Unicode values and symbols.

📝 Formula 27 : T()

T() returns text from a value. If the referenced value is not text (such as a number or date), the function returns an empty text string (""). It is useful for validating text-only data.

📌 Student Data Example

	A	B
1	Value	Result
2	Rahul	=T(A2)
3	2500	=T(A3)

📝 Formula

=T(A2)

📖 Result

Input	Output
Rahul	Rahul
2500	(Blank)

💼 Real-Life Uses

Text Validation
Database Cleaning
Data Verification
Conditional Formulas
Professional Excel Models

❌ Common Mistakes

T() returns blank for numbers.
Dates are stored as numbers, so T() returns blank for dates.
Use ISTEXT() when you only need to check whether a value is text.

💡 Pro Tip: Combine T() with IF(), ISTEXT(), and ISNUMBER() to build smart data validation systems and professional Excel templates.

🔤 Formula 28 : ASC()

ASC() converts full-width (double-byte) characters into half-width (single-byte) characters. It is mainly used in Japanese versions of Microsoft Excel for standardizing imported text.

📌 Example

	A	B
1	Full-width Text	Result
2	ＡＢＣ１２３	=ASC(A2)

📝 Formula

=ASC(A2)

📖 Result

ABC123

💼 Real-Life Uses

Japanese Excel Data
Database Standardization
Importing CSV Files
ERP Systems
Text Cleaning

❌ Common Mistakes

ASC() is mainly available in Japanese versions of Excel.
It has little effect on normal English text.

💡 Pro Tip: Use ASC() when working with Japanese databases that contain double-byte characters.

🈵 Formula 29 : JIS()

JIS() converts half-width (single-byte) characters into full-width (double-byte) characters. It is useful for Japanese document formatting and localization.

📌 Example

	A	B
1	Half-width Text	Result
2	ABC123	=JIS(A2)

📝 Formula

=JIS(A2)

📖 Result

ＡＢＣ１２３

💼 Real-Life Uses

Japanese Reports
Localization
Document Formatting
ERP Systems
Data Standardization

❌ Common Mistakes

JIS() is supported mainly in Japanese Excel editions.
It does not affect standard English text.

💡 Pro Tip: Use JIS() when preparing documents that require full-width Japanese characters.

🔗 Formula 30 : CONCATENATE()

CONCATENATE() joins multiple text strings into one. It is the older version of CONCAT(). While Microsoft recommends using CONCAT() in newer Excel versions, CONCATENATE() is still supported for compatibility with older workbooks.

📌 Employee Name Example

	A	B	C
1	First Name	Last Name	Full Name
2	Rahul	Patel	=CONCATENATE(A2," ",B2)

📝 Formula

=CONCATENATE(A2," ",B2)

📖 Result

Rahul Patel

📌 More Examples

Formula	Result
=CONCATENATE("Excel"," ","Guide")	Excel Guide
=CONCATENATE(A2,"-",B2)	Rahul-Patel
=CONCATENATE("INV-",101)	INV-101

💼 Real-Life Uses

Create Full Names
Merge Addresses
Generate Invoice Numbers
Create Product Codes
Prepare Mailing Lists
Legacy Excel Files

❌ Common Mistakes

CONCATENATE() is an older function.
For new Excel versions, prefer CONCAT() or TEXTJOIN().
Remember to add spaces manually using " ".

⭐ CONCATENATE() vs CONCAT() vs TEXTJOIN()

Function	Best Use
CONCATENATE()	Older Excel Versions
CONCAT()	Modern Excel
TEXTJOIN()	Join Text with Separators & Ignore Blank Cells

💡 Pro Tip: For Microsoft Excel 365 and Excel 2021, use TEXTJOIN() or CONCAT() instead of CONCATENATE() because they are more flexible and support dynamic arrays.

✂️ Formula 31 : TEXTBEFORE()

TEXTBEFORE() extracts all text that appears before a specified delimiter. It is available in Microsoft Excel 365 and Excel 2021.

📌 Email Example

	A	B
1	Email Address	Username
2	rahul@gmail.com	=TEXTBEFORE(A2,"@")

📝 Formula

=TEXTBEFORE(A2,"@")

📖 Result

rahul

💼 Real-Life Uses

Extract Email Username
Remove File Extensions
Extract Product Prefix
Separate Customer IDs
Prepare Reports

💡 Pro Tip: TEXTBEFORE() is much easier than combining LEFT() and FIND().

➡️ Formula 32 : TEXTAFTER()

TEXTAFTER() returns the text that appears after a specified delimiter. It is perfect for extracting domains, file extensions, and product suffixes.

📌 Website Example

	A	B
1	Email	Domain
2	rahul@gmail.com	=TEXTAFTER(A2,"@")

📝 Formula

=TEXTAFTER(A2,"@")

📖 Result

gmail.com

💼 Real-Life Uses

Extract Email Domains
File Extensions
Product Suffixes
Invoice Numbers
Website URLs

💡 Pro Tip: TEXTAFTER() simplifies formulas that previously required RIGHT(), LEN(), and FIND() together.

🪓 Formula 33 : TEXTSPLIT()

TEXTSPLIT() splits one text string into multiple columns or rows using a delimiter. It is one of the most powerful text functions available in Microsoft Excel 365.

📌 Student Name Example

	A	B	C
1	Full Name	First Name	Last Name
2	Rahul Patel	=TEXTSPLIT(A2," ")	Auto Spill

📝 Formula

=TEXTSPLIT(A2," ")

📖 Result

B2	C2
Rahul	Patel

💼 Real-Life Uses

Split Full Names
Separate Addresses
Import CSV Data
Split Product Codes
Database Cleaning
Dynamic Reports

❌ Common Mistakes

Available only in Microsoft Excel 365 and Excel 2021.
Requires enough empty cells for spill results.
If spill range is blocked, Excel returns #SPILL!.

⭐ TEXTBEFORE() vs TEXTAFTER() vs TEXTSPLIT()

Function	Purpose
TEXTBEFORE()	Extract Text Before Delimiter
TEXTAFTER()	Extract Text After Delimiter
TEXTSPLIT()	Split Text into Multiple Cells

💡 Pro Tip: These three formulas replace many complex combinations of LEFT(), RIGHT(), MID(), FIND(), and SEARCH(), making Excel formulas shorter, faster, and easier to understand.

✂️ Formula 34 : LEFT() + FIND()

LEFT() + FIND() extracts text before a specific character or delimiter. This combination is commonly used to extract names, usernames, product prefixes, and file names.

📌 Email Username Example

	A	B
1	Email Address	Username
2	rahul@gmail.com	=LEFT(A2,FIND("@",A2)-1)

📝 Formula

=LEFT(A2,FIND("@",A2)-1)

📖 Result

rahul

💼 Real-Life Uses

Extract Email Usernames
Get Product Prefixes
Separate File Names
Invoice Prefix Extraction
Customer IDs

💡 Pro Tip: Before TEXTBEFORE() was introduced, this was the standard professional solution.

🎯 Formula 35 : MID() + SEARCH()

MID() + SEARCH() extracts text dynamically from the middle of a string. It is useful when the position of the required text changes.

📌 Product Code Example

	A	B
1	Product Code	Category
2	PRD-Mobile-2026	=MID(A2,SEARCH("-",A2)+1,SEARCH("-",A2,SEARCH("-",A2)+1)-SEARCH("-",A2)-1)

📝 Formula

=MID(A2,SEARCH("-",A2)+1,
SEARCH("-",A2,SEARCH("-",A2)+1)-SEARCH("-",A2)-1)

📖 Result

Mobile

💼 Real-Life Uses

Extract Categories
Split Product Codes
Read Invoice Details
Employee Information
Dynamic Data Extraction

💡 Pro Tip: MID() with SEARCH() is one of the most frequently asked combinations in Excel interviews.

➡️ Formula 36 : RIGHT() + LEN()

RIGHT() + LEN() extracts everything after a specified delimiter. It works in all Excel versions and is an excellent alternative to TEXTAFTER().

📌 Email Domain Example

	A	B
1	Email	Domain
2	rahul@gmail.com	=RIGHT(A2,LEN(A2)-FIND("@",A2))

📝 Formula

=RIGHT(A2,LEN(A2)-FIND("@",A2))

📖 Result

gmail.com

💼 Real-Life Uses

Extract Email Domains
Read File Extensions
Website Domains
Invoice Suffixes
Product Categories

❌ Common Mistakes

Forgetting to subtract FIND() from LEN().
If the delimiter is missing, the formula returns an error.
Consider IFERROR() for safer formulas.

⭐ Formula Comparison

Formula	Purpose
LEFT()+FIND()	Extract Text Before Delimiter
MID()+SEARCH()	Extract Middle Text Dynamically
RIGHT()+LEN()	Extract Text After Delimiter

🚀 Interview Tip These three formula combinations are among the most commonly asked Excel interview questions for Data Entry, MIS Executive, Accountant, HR Executive, Data Analyst, and Office Administrator positions.

🔗 Formula 37 : TEXTJOIN() + IF()

TEXTJOIN() + IF() combines only those text values that meet a specific condition. It is extremely useful for creating dynamic reports and filtered text lists.

📌 Passed Students Example

	A	B
1	Student	Result
2	Rahul	Pass
3	Priya	Fail
4	Amit	Pass

📝 Formula

=TEXTJOIN(", ",TRUE,IF(B2:B4="Pass",A2:A4,""))

📖 Result

Rahul, Amit

💼 Real-Life Uses

Create Passed Student Lists
Prepare Employee Reports
Sales Reports
Attendance Reports
Dynamic Dashboards

💡 Pro Tip: TEXTJOIN() + IF() creates professional dynamic text reports automatically.

🚀 Formula 38 : CONCAT() + FILTER()

FILTER() returns only matching records, while CONCAT() combines them into a single text string. This powerful combination is available in Microsoft Excel 365.

📌 Department Example

	A	B
1	Name	Department
2	Rahul	IT
3	Amit	HR
4	Priya	IT

📝 Formula

=TEXTJOIN(", ",TRUE,FILTER(A2:A4,B2:B4="IT"))

📖 Result

Rahul, Priya

💼 Real-Life Uses

Department-wise Employee Lists
Filtered Reports
Dynamic Dashboards
Sales Analysis
HR Reports

💡 Pro Tip: FILTER() + TEXTJOIN() is one of the most powerful dynamic array combinations in Excel 365.

🧹 Formula 39 : SUBSTITUTE() + TRIM()

This combination replaces unwanted text and removes extra spaces at the same time. It is ideal for cleaning imported customer, employee, and product data.

📌 Customer Name Example

	A	B
1	Original Text	Clean Text
2	Rahul___Patel	=TRIM(SUBSTITUTE(A2,"_"," "))

📝 Formula

=TRIM(SUBSTITUTE(A2,"_"," "))

📖 Result

Rahul Patel

💼 Real-Life Uses

Clean Imported Data
Customer Database
Employee Lists
CSV Files
ERP Reports

💡 Pro Tip: Always clean imported Excel data before using XLOOKUP(), VLOOKUP(), or Pivot Tables.

📝 Formula 40 : PROPER() + TRIM()

PROPER() + TRIM() cleans extra spaces and converts names into proper title case automatically.

📌 Student Name Example

	A	B
1	Original Name	Formatted Name
2	rahul patel	=PROPER(TRIM(A2))

📝 Formula

=PROPER(TRIM(A2))

📖 Result

Rahul Patel

💼 Real-Life Uses

Student Records
Employee Database
Customer Lists
CRM Systems
Office Reports
Mail Merge

⭐ Advanced Combination Summary

Formula	Purpose
TEXTJOIN()+IF()	Join Matching Values
FILTER()+TEXTJOIN()	Create Dynamic Lists
SUBSTITUTE()+TRIM()	Clean Imported Data
PROPER()+TRIM()	Format Names Professionally

🚀 Expert Tip These advanced formula combinations are widely used by Data Analysts, HR Executives, Accountants, MIS Executives, and Business Intelligence professionals to automate text processing and create professional Excel dashboards.

📅 Formula 41 : TEXT() + TODAY()

TEXT() + TODAY() formats today's date into a professional text format. It is useful for invoices, reports, certificates, attendance sheets, and dashboards.

📌 Report Date Example

	A	B
1	Today's Date	Formatted Date
2	=TODAY()	=TEXT(TODAY(),"dd-mmm-yyyy")

📝 Formula

=TEXT(TODAY(),"dd-mmm-yyyy")

📖 Result

06-Aug-2026

⏰ Formula 42 : TEXT() + NOW()

TEXT() + NOW() formats the current date and time into a readable format for attendance, reports, and dashboards.

📌 Attendance Example

	A	B
1	Current Date & Time	Formatted
2	=NOW()	=TEXT(NOW(),"dd-mmm-yyyy hh:mm AM/PM")

📝 Formula

=TEXT(NOW(),"dd-mmm-yyyy hh:mm AM/PM")

📖 Result

06-Aug-2026 10:30 AM

✏️ Formula 43 : REPLACE() + FIND()

This combination finds specific text and replaces it dynamically. It is useful for correcting product names, invoice numbers, and imported records.

📌 Product Example

	A	B
1	Product	Updated
2	Phone Old Model	=REPLACE(A2,FIND("Old",A2),3,"New")

📖 Result

Phone New Model

🔍 Formula 44 : SEARCH() + ISNUMBER()

SEARCH() + ISNUMBER() checks whether a word exists in a text string and returns TRUE or FALSE.

📌 Product Search Example

	A	B
1	Product Name	Contains Laptop?
2	Gaming Laptop	=ISNUMBER(SEARCH("Laptop",A2))

📝 Formula

=ISNUMBER(SEARCH("Laptop",A2))

📖 Result

TRUE

💼 Real-Life Uses

Keyword Search
Product Identification
Email Validation
Customer Records
Data Filtering

✔️ Formula 45 : EXACT() + IF()

This combination compares two text values and displays a custom message instead of TRUE or FALSE.

📌 Password Example

	A	B	C
1	Password	Confirm	Status
2	Excel123	Excel123	=IF(EXACT(A2,B2),"Matched","Not Matched")

📝 Formula

=IF(EXACT(A2,B2),"Matched","Not Matched")

📖 Result

Matched

🚀 Office Tip These advanced combinations are frequently used in HR systems, MIS reports, ERP software, customer databases, sales reports, and Excel interview practical tests.

🧹 Formula 46 : CLEAN() + TRIM()

CLEAN() + TRIM() removes hidden non-printable characters and extra spaces from imported data. This is one of the best formulas for preparing data before using XLOOKUP(), Pivot Tables, or Charts.

📌 Imported Data Example

	A	B
1	Imported Name	Clean Name
2	Rahul Patel	=TRIM(CLEAN(A2))

📝 Formula

=TRIM(CLEAN(A2))

📖 Result

Rahul Patel

💼 Real-Life Uses

Import CSV Files
Clean Website Data
Prepare Data for XLOOKUP()
HR Databases
Financial Reports

💯 Formula 47 : VALUE() + SUBSTITUTE()

This formula converts text numbers containing commas, currency symbols, or spaces into real numeric values for calculations.

📌 Sales Example

	A	B
1	Text Number	Numeric Value
2	25,000	=VALUE(SUBSTITUTE(A2,",",""))

📝 Formula

=VALUE(SUBSTITUTE(A2,",",""))

📖 Result

25000

💼 Real-Life Uses

Convert Imported Numbers
Accounting
Sales Reports
Payroll
Financial Dashboards

📊 Formula 48 : TEXTSPLIT() + INDEX()

TEXTSPLIT() + INDEX() extracts a specific word from a text string after splitting it. This formula is available in Microsoft Excel 365.

📌 Student Name Example

	A	B
1	Full Name	Last Name
2	Rahul Patel	=INDEX(TEXTSPLIT(A2," "),2)

📝 Formula

=INDEX(TEXTSPLIT(A2," "),2)

📖 Result

Patel

💼 Real-Life Uses

Extract Last Names
Split Addresses
Customer Databases
Student Records
Employee Lists

🏢 Formula 49 : Real-Life Office Project

Create a professional employee database using Excel text formulas.

Task	Formula
Clean Employee Name	=PROPER(TRIM(A2))
Extract Email Username	=LEFT(B2,FIND("@",B2)-1)
Extract Domain	=RIGHT(B2,LEN(B2)-FIND("@",B2))
Format Salary	=TEXT(C2,"₹ #,##0")
Create Full Address	=TEXTJOIN(", ",TRUE,D2:F2)

💼 Skills Learned

Data Cleaning
Email Processing
Salary Formatting
Dynamic Reports
Professional Office Automation

🏆 Formula 50 : Ultimate Text Formula Challenge

Combine multiple Excel text formulas to build a complete professional employee information system.

📌 Challenge

Requirement	Formula
Clean Employee Name	=PROPER(TRIM(A2))
Extract Username	=TEXTBEFORE(B2,"@")
Extract Domain	=TEXTAFTER(B2,"@")
Convert Salary	=VALUE(SUBSTITUTE(C2,",",""))
Create Address	=TEXTJOIN(", ",TRUE,D2:F2)

🎯 Congratulations!

You have successfully completed the Top 50 Microsoft Excel Text Formulas. You can now clean data, extract text, combine values, create reports, automate office work, and solve real-world Excel problems with confidence.

🚀 Pro Tip

Practice these formulas regularly with real datasets. Combining multiple text functions together is one of the fastest ways to improve your Excel skills and prepare for office work, MIS reporting, HR tasks, data analysis, and job interviews.

🎉 Congratulations! You Have Mastered the Top 50 Excel Text Formulas

You have successfully completed the Top 50 Microsoft Excel Text Formulas from beginner to advanced level. These formulas are widely used in offices, HR departments, accounting, finance, MIS reporting, business analysis, customer databases, and data cleaning.

📚 Quick Text Formula Cheat Sheet

Category	Popular Formulas
Extract Text	LEFT(), RIGHT(), MID()
Text Length	LEN()
Formatting	UPPER(), LOWER(), PROPER()
Join Text	CONCAT(), TEXTJOIN()
Search Text	FIND(), SEARCH()
Clean Data	TRIM(), CLEAN(), SUBSTITUTE()
Advanced	TEXTBEFORE(), TEXTAFTER(), TEXTSPLIT()

❌ Common Excel Text Formula Mistakes

Ignoring extra spaces before using lookup formulas.
Using FIND() instead of SEARCH() when case sensitivity is not required.
Not cleaning imported data before analysis.
Forgetting separators while combining text.
Using old CONCATENATE() instead of CONCAT() or TEXTJOIN().
Ignoring hidden characters from web or PDF data.

❓ Frequently Asked Questions (FAQ)

Which Excel text formula is used the most?

LEFT(), RIGHT(), MID(), LEN(), TRIM(), CONCAT(), TEXTJOIN(), FIND(), SEARCH(), and SUBSTITUTE() are among the most frequently used text formulas.

Which text formula is best for beginners?

Start with LEFT(), RIGHT(), LEN(), TRIM(), and CONCAT(). Then gradually move to advanced formulas such as TEXTSPLIT(), TEXTAFTER(), and TEXTBEFORE().

Are these formulas useful for job interviews?

Yes. These formulas are commonly asked in Excel interviews for MIS Executive, Accountant, HR Executive, Data Analyst, and Office Assistant roles.

Which Excel version supports all these formulas?

Microsoft Excel 365 and Excel 2021 support all modern text formulas including TEXTBEFORE(), TEXTAFTER(), and TEXTSPLIT().

🏆 Final Conclusion

Mastering Excel Text Formulas helps you clean messy data, extract important information, combine multiple values, automate reports, and improve productivity. Whether you are a student, office employee, accountant, HR executive, or data analyst, these formulas will help you work faster and more accurately. Practice regularly with real datasets to become an Excel expert.

🚀 Continue Learning

Top 50 Excel Lookup Formulas
Top 50 Excel Logical Formulas
Top 50 Excel Date & Time Formulas
Top 50 Excel Financial Formulas
100+ Excel Keyboard Shortcuts
Excel Interview Questions & Answers

⭐ Thank You for Reading!

If this guide helped you, please share it with your friends, classmates, and colleagues.

📌 Bookmark this page for future reference.

💬 Have questions? Leave a comment below.

🚀 Keep Learning with Smart AI Profit Hub

Last Updated: August 2026 • This guide is regularly updated with the latest Microsoft Excel Text Functions and Microsoft 365 features.

Smart AI Profit Hub

Top 50 Microsoft Excel Text Formulas with Examples (Beginner to Advanced) – Complete Excel Text Function Guide 2026

Top 50 Microsoft Excel Text Formulas with Examples (Beginner to Advanced)

📘 What You Will Learn

📚 Top 50 Excel Text Formulas List

🚀 Let's Start Learning

🔤 Formula 1 : LEFT()

📌 Student Roll Number Example

📝 Formula

📖 Formula Breakdown

📌 More LEFT() Examples

💼 Real-Life Uses

❌ Common Mistakes

📝 Practice Exercise

➡️ Formula 2 : RIGHT()

📌 Student Roll Number Example

📝 Formula

📖 Formula Breakdown

📌 More RIGHT() Examples

💼 Real-Life Uses

❌ Common Mistakes

📝 Practice Exercise

🔠 Formula 3 : MID()

📌 Example

📝 Formula

📖 Result

📌 Breakdown

💼 Uses

❌ Common Mistakes

📏 Formula 4 : LEN()

📌 Example

📝 Formula

📖 Result

📌 Breakdown

💼 Uses

❌ Common Mistakes

🧹 Formula 5 : TRIM()

📌 Student Name Example

📝 Formula

📖 Result

📌 Breakdown

💼 Real-Life Uses

❌ Common Mistakes

🔠 Formula 6 : UPPER()

📌 Employee Name Example

📝 Formula

📖 Result

📌 Breakdown

💼 Real-Life Uses

❌ Common Mistakes

🔡 Formula 7 : LOWER()

📌 Email Example

📝 Formula

📖 Result

💼 Real-Life Uses

📝 Formula 8 : PROPER()

📌 Student Name Example

📝 Formula

📖 Result

💼 Real-Life Uses

🔗 Formula 9 : CONCAT()

📌 Student Full Name Example

📝 Formula

📖 Result

💼 Real-Life Uses

❌ Common Mistakes

🔗 Formula 10 : TEXTJOIN()

📌 Student Full Address Example

📝 Formula

📖 Result

💼 Real-Life Uses

💲 Formula 11 : TEXT()

📌 Salary Example

📝 Formula

📖 Result

💼 Real-Life Uses

🔍 Formula 12 : FIND()

📌 Email Example

📝 Formula

📖 Result