2.3 Text & Date Manipulation (Data Wrangling Secrets)

Module 2: Advanced Formulas & Functions – Expanded » 2.3 Text & Date Manipulation (Data Wrangling Secrets)

Text Content

Why This Matters

27% of analyst time is spent cleaning data (Harvard Business Review)
AI prompt engineering now requires structured text data

1. Text Functions That Save Hours

Function	Magic Trick	Business Application
TEXTJOIN	`=TEXTJOIN(", ",TRUE,Emails)`	Creating mailing lists from CRM data
CONCAT	`=CONCAT(LEFT(Name,1),".",Surname)`	Generating usernames automatically
SUBSTITUTE	`=SUBSTITUTE(Comment, "USD", "$")`	Standardizing financial reports

2. Date Intelligence for Planning

=WORKDAY.INTL(StartDate, Days, "0000011")  // Skips weekends & custom holidays  
=EDATE(ContractDate, 12)                   // Find 1-year anniversary automatically

HR Case Study:

Calculate employee probation end dates accounting for company holidays

Assignment:

Clean a 500-row dataset of international transactions where dates are in 7 different formats

Added on July 08, 2025

Module Contents

Course Navigation

Course Overview Module 1: Foundations of Spreadsheet Mastery – Expanded Module 2: Advanced Formulas & Functions – Expanded Module 3: Data Visualization & Business Intelligence – Expanded Module 4: Automation & Efficiency – Expanded Module 5: Financial Modeling – Expanded Module 6: Collaboration & Enterprise – Expanded Capstone Module: Real-World Mastery Projects