Course Content
Basic Of Excel
0/2
Understanding Cell References
00:00
wildcard characters in Excel
00:00
Text Formulas
0/1
Text Formula
Mathematical Functions
SumIf, SumIfs CountIf, CountIfs AverageIf, AverageIfs, Nested IF, IFERROR Statement, AND, OR, NOT
0/3
Excel SUMIFS Function
Wildcard Characters in Excel
SUMIF Function
Advanced Excel
About Lesson

Category 1: Text Length & Case Conversion

Formulas:

  1. LEN(text) – Counts characters in a cell.

  2. LOWER(text) – Converts text to lowercase.

  3. UPPER(text) – Converts text to uppercase.

  4. PROPER(text) – Capitalizes the first letter of each word.

  5. 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:

  1. LEFT(text, num_chars) – Gets text from the left.

  2. RIGHT(text, num_chars) – Gets text from the right.

  3. MID(text, start, num_chars) – Gets a part from the middle.

  4. TEXTBEFORE(text, delimiter) – Gets text before a specific character (Excel 365+).

  5. 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:

  1. FIND(find_text, within_text) – Finds position (case-sensitive).

  2. 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:

  1. CONCATENATE(text1, text2, …) – Joins values (older version).

  2. TEXTJOIN(delimiter, ignore_empty, text1, …) – Joins with delimiter (modern).

  3. & – Joins two or more text values.

  4. CONCAT(text1, [text2], …)
    Replaces CONCATENATE() 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:

  1. TEXT(number, format_text) – Converts number to formatted text.

  2. 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:

  1. 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
Previous
Next