Day 13: Advanced Text Functions – Combining, Cleaning, and Transforming Text

Welcome to Day 13 of our Excel learning journey! Yesterday, we explored how to dynamically visualize data with conditional formatting. Today, we’ll shift our focus to advanced text functions—essential tools for combining, cleaning, and transforming text data.

These functions are perfect for tasks like merging names, standardizing formatting, or cleaning messy datasets. Let’s dive in!

---

Why Use Text Functions?

Text functions in Excel allow you to:

• Combine data from multiple cells.
• Standardize inconsistent text formats (e.g., proper capitalization).
• Extract and manipulate text for analysis.

Whether you're working with names, addresses, or product descriptions, these functions can save hours of manual effort.

---

Key Text Functions: CONCAT, TEXTJOIN, and PROPER

1. CONCAT: Combining Text

The CONCAT function (short for “concatenate”) combines text from multiple cells into one.

Syntax:
=CONCAT(text1, [text2], ...)

Example:

First Name	Last Name
John	Doe
Alice	Smith

To combine first and last names into a single cell:

• Enter =CONCAT(A2, " ", B2) in a new column.
• Result: John Doe (the space is added manually between the first and last name).

---

2. TEXTJOIN: Joining Text with a Delimiter

The TEXTJOIN function combines text from multiple cells, separated by a delimiter of your choice.

Syntax:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

Example:

Category	Subcategories
Food	Fruits, Vegetables, Dairy

To join subcategories with a comma and space:

• Enter =TEXTJOIN(", ", TRUE, "Fruits", "Vegetables", "Dairy").
• Result: Fruits, Vegetables, Dairy.

Pro Tip: Set ignore_empty to TRUE to skip blank cells.

---

3. PROPER: Standardizing Capitalization

The PROPER function capitalizes the first letter of each word in a text string.

Syntax:
=PROPER(text)

Example:
To fix inconsistent capitalization in a dataset:

Name
john SMITH
ALICE doe

• Enter =PROPER(A2) in a new column.
• Result: John Smith or Alice Doe.

---

Additional Functions for Text Manipulation

• LEFT, RIGHT, MID: Extract specific parts of a text string (e.g., the first 5 characters).
  • Example: =LEFT("Excel", 2) → Ex.
• LEN: Count the number of characters in a cell.
  • Example: =LEN("Excel") → 5.
• TRIM: Remove extra spaces from text.
  • Example: =TRIM(" Hello ") → Hello.

---

Practical Example

Use the following dataset to practice:

First Name	Last Name	Email	Phone Number
John	Doe	john.doe@gmail.com	123 456 7890
Alice	Smith	alice.smith@company.com	987-654-3210
Bob	Brown	bob.brown@workplace.org

Exercise 1: Combine First and Last Names

Combine first and last names into a single column using CONCAT:

• Formula: =CONCAT(A2, " ", B2).
• Result: John Doe, Alice Smith, etc.

Exercise 2: Create a Contact Summary

Use TEXTJOIN to create a contact summary (name, email, phone number):

• Formula: =TEXTJOIN(", ", TRUE, CONCAT(A2, " ", B2), C2, D2).
• Result: John Doe, john.doe@gmail.com, 123 456 7890.

Exercise 3: Standardize Phone Numbers

Use TRIM to remove extra spaces from phone numbers:

• Formula: =TRIM(D2).

---

Pro Tips for Using Text Functions

• Combine Functions: Use CONCAT with PROPER to merge names and standardize capitalization.
  • Example: =PROPER(CONCAT(A2, " ", B2)).
• Use Helper Columns: Perform transformations in helper columns before replacing the original data.
• Use TEXTJOIN for Dynamic Lists: Combine non-contiguous data without leaving blank gaps.

---

Common Mistakes to Avoid

• Forgetting to Add Spaces: Always include spaces (e.g., " ") when combining text for readability.
• Not Using Ignore_Empty: When using TEXTJOIN, set ignore_empty to TRUE to avoid unwanted separators between blank cells.
• Overwriting Data: Avoid overwriting raw data; always work in separate columns.

---

SEO Keywords:

• How to use CONCAT in Excel
• TEXTJOIN function examples
• Fix capitalization in Excel with PROPER
• Excel text manipulation formulas
• Combining text in Excel

---