Text Functions
Introduction: Why Text Functions Matter?
When working with real-world data like names, emails, product codes, or tags, you’ll often need to extract parts of text, combine strings, or break text into columns.
These functions help automate such tasks without manual copy-paste.
Step 1: Sample Table for Practice
| Full Name | Email Address | Code | First Name | Last Name | Email Prefix | Length | Code Start | Code End | Tags | Split Tag 1 |
|---|---|---|---|---|---|---|---|---|---|---|
| Aayush Mehra | aayush@codersdaily.in | AB-234-XZ | python,sql,data analysis | |||||||
| Riya Sharma | riya.sharma@gmail.com | CD-987-YT | java,cloud,api | |||||||
| Aman Tiwari | aman.t@outlook.com | EF-120-ZP | ml,ai,python |
We'll now use different text functions to fill in the empty columns.
Step 2: LEFT(text, number_of_characters)
Use Case: Extract first 2 characters of the Code.
📍 Formula in Code Start (H2):
=LEFT(C2, 2)
Returns "AB" from "AB-234-XZ".
Step 3: RIGHT(text, number_of_characters)
Use Case: Extract last 2 characters of the Code.
📍 Formula in Code End (I2):
=RIGHT(C2, 2)
Returns "XZ" from "AB-234-XZ".
Step 4: LEN(text)
Use Case: Count number of characters in Full Name.
📍 Formula in Length (G2):
=LEN(A2)
Counts characters including space.
Step 5: SPLIT(text, delimiter)
Use Case: Split full name into First Name and Last Name.
📍 Formula in First Name (D2):
=SPLIT(A2, " ")
This will split "Aayush Mehra" into Aayush and Mehra.
📝 You can also split Tags:
📍 Formula in Split Tag 1 (K2):
=SPLIT(J2, ",")
Step 6: CONCAT(value1, value2)
Use Case: Join first name and last name.
📍 Formula in New Column (say L2):
=CONCAT(D2, E2)
Joins without space → AayushMehra
(You can use &" " to add a space: =D2 & " " & E2)
Step 7: TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
Use Case: Combine tags into a single line, even if some are blank.
📍 Formula:
=TEXTJOIN(", ", TRUE, "python", "", "sql", "cloud")
It will join only non-empty ones with comma:
python, sql, cloud
For dynamic case:
=TEXTJOIN(", ", TRUE, SPLIT(J2, ","))
Mini Project: Build a Contact Parsing Tool
Let students create a Contact Sheet Parser with:
-
Names broken into first and last
-
Extract username from email using
=LEFT(B2,FIND("@",B2)-1) -
Join them in different formats (like Full Name with Dash)
-
Analyze code prefixes (region codes) from product codes
