📌 Introduction to Excel Text Functions
Why Text Functions Matter in Excel
When people think of Excel, numbers usually come to mind. But let’s be real — not everything is about numbers. Sometimes, it’s all about names, IDs, addresses, or product codes — all text. That’s where Excel’s text functions come into play. Whether you’re cleaning messy data or building reports, text formulas save you time, reduce manual work, and help keep your data tidy.
Basic vs. Advanced Text Functions
There are basic formulas like LEFT and RIGHT, and then there are advanced ones like TEXTJOIN and SUBSTITUTE. We’ll walk through them all — step by step — with examples that make sense.
🚀 Essential Excel Text Functions Explained
1. CONCAT / CONCATENATE
Syntax:
=CONCAT(text1, [text2], …)
Example:
You have:
- A1: John
- B1: Doe
Use:
=CONCAT(A1, ” “, B1)
Result: John Doe
✅ CONCATENATE is the older version. CONCAT is the newer and smarter one (supports ranges).
2. TEXTJOIN
Syntax:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
Example:
You have:
- A1: Apple
- B1: Mango
- C1: Orange
Use:
=TEXTJOIN(“, “, TRUE, A1:C1)
Result: Apple, Mango, Orange
📌 Great for creating comma-separated values from a list.
3. LEFT, RIGHT, and MID
LEFT Syntax:
=LEFT(text, num_chars)
RIGHT Syntax:
=RIGHT(text, num_chars)
MID Syntax:
=MID(text, start_num, num_chars)
Example:
Text in A1: ABC123XYZ
=LEFT(A1, 3) → “ABC”
=RIGHT(A1, 3) → “XYZ”
=MID(A1, 4, 3) → “123”
💡 Useful for extracting ID codes or parts of a string.
4. LEN
Syntax:
=LEN(text)
Example:
Text in A1: Hello
=LEN(A1) → 5
📏 Measures the number of characters in a cell (spaces included!).
5. FIND and SEARCH
FIND Syntax:
=FIND(find_text, within_text, [start_num])
SEARCH Syntax:
=SEARCH(find_text, within_text, [start_num])
Difference:
- FIND is case-sensitive
- SEARCH is not
Example:
A1: ExcelIsGreat
=FIND(“I”, A1) → 6
=SEARCH(“i”, A1) → 6
6. UPPER, LOWER, PROPER
Examples:
=UPPER(“excel”) → “EXCEL”
=LOWER(“EXCEL”) → “excel”
=PROPER(“excel is fun”) → “Excel Is Fun”
🔤 Change text casing with one formula.
7. TRIM
Syntax:
=TRIM(text)
Example:
A1: ” Hello World “
=TRIM(A1) → “Hello World”
🧹 Cleans up unwanted spaces from text.
8. SUBSTITUTE
Syntax:
=SUBSTITUTE(text, old_text, new_text, [instance_num])
Example:
A1: A-B-C-D
=SUBSTITUTE(A1, “-“, “/”) → `A/B/C/D`
🔄 Replace one text with another. Great for cleaning formats.
9. REPLACE
Syntax:
=REPLACE(old_text, start_num, num_chars, new_text)
Example:
A1: 123456789
=REPLACE(A1, 4, 3, “ABC”) → `123ABC789`
🛠️ Use this to replace by position rather than matching text.
10. TEXT
Syntax:
=TEXT(value, format_text)
Example:
=TEXT(TODAY(), “dd-mmm-yyyy”) → “30-Jul-2025”
=TEXT(1234.56, “$#,##0.00”) → “$1,234.56”
📅 Format numbers and dates into clean text outputs.
11. VALUE
Syntax:
=VALUE(text)
Example:
A1: “123.45”
=VALUE(A1) → 123.45 (as number)
🔢 Converts text into usable numeric format.
12. EXACT
Syntax:
=EXACT(text1, text2)
Example:
=EXACT(“Excel”, “excel”) → FALSE
=EXACT(“Excel”, “Excel”) → TRUE
✅ Checks if two strings are exactly the same — case-sensitive!
13. CHAR and CODE
CHAR Syntax:
=CHAR(number)
CODE Syntax:
=CODE(text)
Example:
=CHAR(65) → “A”
=CODE(“A”) → 65
🧬 Helpful when working with ASCII characters.
14. REPT
Syntax:
=REPT(text, number_times)
Example:
=REPT(“*”, 10) → **********
⭐ Great for visual dashboards and formatting.
15. T and ISTEXT
T Syntax:
=T(value)
ISTEXT Syntax:
=ISTEXT(value)
Examples:
=T(“Excel”) → “Excel”
=ISTEXT(123) → FALSE
✅ Helps validate or filter text data in formulas.
🎯 Tips to Master Excel Text Formulas
Combine Text Functions
You can nest functions like:
=PROPER(TRIM(A1))
to clean and format a string in one go.
Use Helper Columns for Complex Tasks
Instead of long nested formulas, split logic into multiple columns for clarity and debugging.
✅ Conclusion
Text formulas in Excel are your secret weapon when working with non-numeric data. From cleaning inputs to formatting outputs, these tools turn chaos into clarity. Whether you’re managing a database, cleaning survey results, or automating reports — mastering these formulas will save hours of work.
Start small. Practice each. Then combine them like Lego blocks to solve real-world problems.
❓FAQs
1. What is the difference between CONCAT and CONCATENATE?
CONCAT is the newer, improved version. It supports ranges and works better with dynamic arrays, while CONCATENATE is older and limited.
2. Can I extract text after a specific character?
Yes! Combine FIND, LEN, and MID to extract text dynamically after a symbol like @ or -.
3. How do I remove extra spaces in Excel?
Use the TRIM function. It removes all leading, trailing, and multiple spaces between words.
4. What’s the best way to join text from multiple cells?
TEXTJOIN is the most flexible. It lets you set delimiters and ignore empty cells.
5. Why is my formula returning a #VALUE! error?
It usually means incompatible data types. Use VALUE to convert text to numbers or check for blank cells.
