Category 1: Text Length & Case Conversion
Formulas:
-
LEN(text)– Counts characters in a cell. -
LOWER(text)– Converts text to lowercase. -
UPPER(text)– Converts text to uppercase. -
PROPER(text)– Capitalizes the first letter of each word. -
TRIM(text)– Removes extra spaces from the text.
| Name (A2) | =LEN(A2) | =LOWER(A2) | =UPPER(A2) | =PROPER(A2) | =TRIM(A2) |
|---|---|---|---|---|---|
| RAHUL SEN | 10 | rahul sen | RAHUL SEN | Rahul Sen | RAHUL SEN |
| priya das | 9 | priya das | PRIYA DAS | Priya Das | priya das |
| Arjun MEHTA | 11 | arjun mehta | ARJUN MEHTA | Arjun Mehta | Arjun MEHTA |
| SNEHA roy | 9 | sneha roy | SNEHA ROY | Sneha Roy | SNEHA roy |
| RAVI SHAH | 13 | ravi shah | RAVI SHAH | Ravi Shah | RAVI SHAH |
Category 2: Extracting Parts of Text
Formulas:
-
LEFT(text, num_chars)– Gets text from the left. -
RIGHT(text, num_chars)– Gets text from the right. -
MID(text, start, num_chars)– Gets a part from the middle. -
TEXTBEFORE(text, delimiter)– Gets text before a specific character (Excel 365+). -
TEXTAFTER(text, delimiter)– Gets text after a specific character (Excel 365+).
Example Table:
| Full Name | =LEFT(A2,5) |
=RIGHT(A2,3) |
=MID(A2,7,4) |
=TEXTBEFORE(A2," ") |
=TEXTAFTER(A2," ") |
|---|---|---|---|---|---|
| Rahul Sen | Rahul | Sen | l Sen | Rahul | Sen |
| Priya Das | Priya | Das | a Das | Priya | Das |
| Arjun Mehta | Arjun | hta | Meht | Arjun | Mehta |
| Sneha Roy | Sneha | Roy | a Roy | Sneha | Roy |
Download this task example file
Category 3: Text Search & Positioning
Formulas:
-
FIND(find_text, within_text)– Finds position (case-sensitive). -
SEARCH(find_text, within_text)– Finds position (not case-sensitive).
Example Table:
| City | =FIND("a", A2) |
=SEARCH("A", A2) |
|---|---|---|
| Kolkata | 6 | 6 |
| Delhi | #VALUE! | #VALUE! |
| Mumbai | 3 | 3 |
| Bangalore | 2 | 2 |
FIND vs. SEARCH vs. Find Tool in Excel
| Feature | FIND Function | SEARCH Function |
|---|---|---|
| What it is | Worksheet Function | Worksheet Function |
| Primary Purpose | Data manipulation & extraction within formulas. | Data manipulation & extraction within formulas. |
| Output | Returns a number (starting position of found text). | Returns a number (starting position of found text). |
| Case-Sensitive? | Yes. FIND("X", "text") will return an error. |
No. SEARCH("X", "text") will find “x”. |
| Wildcards Allowed? | No. Treats ? and * as literal characters. |
Yes. ? (any single char), * (any sequence of chars). |
| Dynamic & Automatic | Yes. Recalculates automatically when source data changes. | Yes. Recalculates automatically when source data changes. |
| Best For | Extracting text when case is important (e.g., passwords, codes). | General text parsing (e.g., splitting names, finding substrings). |
| Integration | Used within formulas (e.g., with LEFT, RIGHT, MID, IF). | Used within formulas (e.g., with LEFT, RIGHT, MID, IF). |
| Use in Tables | Creates calculated columns to automate data processing. | Creates calculated columns to automate data processing. |
| Error Handling | Returns #VALUE! if text is not found. |
Returns #VALUE! if text is not found. |
| Pros | • Case-sensitive for precise matches. • Automates workflows. |
• Flexible with wildcards and case. • Automates workflows. |
| Cons | • Not for navigation. • Requires combining with other functions to be useful. |
• Not for navigation. • Cannot make case-sensitive distinctions. |
Download the Task file
Category 4: Combining or Joining Text
Formulas:
-
CONCATENATE(text1, text2, …)– Joins values (older version). -
TEXTJOIN(delimiter, ignore_empty, text1, …)– Joins with delimiter (modern). -
&– Joins two or more text values. CONCAT(text1, [text2], …)
ReplacesCONCATENATE()in Excel 2016 and newer. Joins multiple text items (including ranges), but without a delimiter.
Example Table:
| Name | City | Pincode | =A2 & ", " & B2 |
=TEXTJOIN(", ", TRUE, A2, B2, C2) |
|---|---|---|---|---|
| Rahul Sen | Kolkata | 700001 | Rahul Sen, Kolkata | Rahul Sen, Kolkata, 700001 |
| Priya Das | Delhi | 110001 | Priya Das, Delhi | Priya Das, Delhi, 110001 |
| Arjun Mehta | Mumbai | 400001 | Arjun Mehta, Mumbai | Arjun Mehta, Mumbai, 400001 |
| Sneha Roy | Bangalore | 560001 | Sneha Roy, Bangalore | Sneha Roy, Bangalore, 560001 |
Comparison Table
| Feature | CONCATENATE() |
TEXTJOIN() |
& Operator |
CONCAT() |
|---|---|---|---|---|
| Supports delimiter | ❌ No | ✅ Yes | ❌ No | ❌ No |
| Ignores blanks | ❌ No | ✅ Yes | ❌ No | ❌ No |
| Joins ranges | ❌ No | ✅ Yes | ❌ No | ✅ Yes |
| Available in Excel | All versions | Excel 2016+ | All versions | Excel 2016+ |
| Supports arrays | ❌ No | ✅ Yes | ❌ No | ✅ Yes |
| Ease of use | ✅ Easy | ⚠️ Medium | ✅ Very Easy | ✅ Easy |
| Handles dynamic ranges | ❌ No | ✅ Yes | ❌ No | ✅ Yes |
| Recommended for | Legacy files | Merging large ranges | Quick two-cell merge | Simple modern joins |
Quick Use Case Table
| Use Case | Best Function | Example |
|---|---|---|
| Join two cells quickly | & or CONCAT() |
=A2 & B2 or =CONCAT(A2, B2) |
| Join cells with a space or comma | TEXTJOIN() |
=TEXTJOIN(" ", TRUE, A2:C2) |
| Backward-compatible merging | CONCATENATE() |
=CONCATENATE(A2, " ", B2) |
| Join range with delimiter, skip blanks | TEXTJOIN() |
=TEXTJOIN(", ", TRUE, A2:A10) |
| Join a simple range without delimiter | CONCAT() |
=CONCAT(A2:A5) |
| Fast typing without a function | & |
=A2 & "-" & B2 |
Pros and Cons
| Function | Pros | Cons |
|---|---|---|
& |
Fast, easy, universal | No range support, no delimiter, messy for long joins |
CONCATENATE() |
Easy, backward-compatible | Deprecated, no range/delimiter/blank support |
TEXTJOIN() |
Powerful, handles ranges/delimiters/blanks | Only in newer Excel, more complex |
CONCAT() |
Modern replacement, supports ranges, simpler than TEXTJOIN() |
No delimiter or ignore blanks functionality |
📌 Recommendations:
-
Use
TEXTJOIN()if:-
You want to join with delimiters and ignore blanks.
-
You’re using Excel 2016 or later.
-
-
Use
CONCAT()if:-
You don’t need a delimiter but want to join many values or ranges.
-
-
Use
&if:-
You need a quick join of just two or three values.
-
-
Use
CONCATENATE()only if:-
You’re working in an older Excel version or need compatibility.
-
Download the Task File
Category 6: Convert Between Text & Numbers
Formulas:
-
TEXT(number, format_text)– Converts number to formatted text. -
VALUE(text)– Converts text to number.
Example Table:
| Pincode (Text) | Contact No (Text) | =VALUE(B2) |
=TEXT(A2, "000000") |
|---|---|---|---|
| 700001 | “9876543210” | 9876543210 | 700001 |
| 110001 | “9123456789” | 9123456789 | 110001 |
| 400001 | “9988776655” | 9988776655 | 400001 |
| 560001 | “9090909090” | 9090909090 | 560001 |
Category 7: Exact Match & Text Comparison
Formulas:
-
EXACT(text1, text2)– Checks if text is exactly the same (case-sensitive).
Example Table:
| Text 1 | Text 2 | =EXACT(A2, B2) |
|---|---|---|
| Rahul | RAHUL | FALSE |
| Delhi | Delhi | TRUE |
| Mumbai | mumbai | FALSE |
| Bangalore | Bangalore | TRUE |