Module 6
Text transformations
Trim, clean, change case, split, merge, extract and replace text with exact semantics.
You'll be able to
- Standardise messy text
- Split and merge columns
- Extract parts of a value
Lessons
- 1.Trim, clean and capitalise textStandardise text so equal things compare equal.
- 2.Split columns and extract text by delimiterSplit and extract text in a way that works for values of any length.
- 3.Replace values exactlyReplace values without accidentally changing partial matches.
- 4.Combine text values and handle missing partsBuild combined text such as full names and labels without stray nulls or separators.
Take the text transformations quiz (8 questions)
Exercises
- beginner~8 minPro
Clean names and build a full name (logistics)
First and last names have stray spaces and random capitals. Clean them and combine into one Full Name column.
- beginner~8 minPro
Clean names and build a full name (software)
First and last names have stray spaces and random capitals. Clean them and combine into one Full Name column.
- beginner~8 minPro
Clean names and build a full name (hospital)
First and last names have stray spaces and random capitals. Clean them and combine into one Full Name column.
- beginner~6 minPro
Split email addresses into user and domain (logistics)
Add the part before @ as User and the part after @ as Domain.
- beginner~6 minPro
Split email addresses into user and domain (software)
Add the part before @ as User and the part after @ as Domain.
- beginner~6 minPro
Split email addresses into user and domain (hospital)
Add the part before @ as User and the part after @ as Domain.
- beginner~6 minPro
Split customer names into first and last name (kite shop)
Split Customer at the space into First Name and Last Name columns.
- beginner~6 minPro
Split customer names into first and last name (bakery)
Split Customer at the space into First Name and Last Name columns.
- beginner~6 minPro
Split customer names into first and last name (bike rental)
Split Customer at the space into First Name and Last Name columns.
- beginner~6 minPro
Replace channel names with short codes (telecom)
Turn Email, Phone and Chat into EM, PH and CH for a compact dashboard.
- beginner~6 minPro
Replace channel names with short codes (saas)
Turn Email, Phone and Chat into EM, PH and CH for a compact dashboard.
- intermediate~6 minPro
Build fixed-width badge numbers (logistics)
Turn employee IDs like E104 into badge numbers like BADGE-000104.
- intermediate~6 minPro
Build fixed-width badge numbers (software)
Turn employee IDs like E104 into badge numbers like BADGE-000104.
- intermediate~6 minPro
Build fixed-width badge numbers (hospital)
Turn employee IDs like E104 into badge numbers like BADGE-000104.
- intermediate~7 minPro
Flag tickets that mention billing, in any case (telecom)
Add an Is Billing flag when Topic contains "bill" regardless of capitalisation.
- intermediate~7 minPro
Flag tickets that mention billing, in any case (saas)
Add an Is Billing flag when Topic contains "bill" regardless of capitalisation.
- intermediate~7 minPro
Extract the reference code from email subjects (kitchens)
Each subject line contains a reference like (ref: KT-0042) somewhere in it. Pull out just the code.
- intermediate~7 minPro
Extract the reference code from email subjects (repairs)
Each subject line contains a reference like (ref: KT-0042) somewhere in it. Pull out just the code.
- beginner~6 minPro
Keep only the digits of phone numbers (dental)
Phone numbers were typed with spaces, brackets, dots, dashes and plus signs. Keep just the digits.
- beginner~6 minPro
Keep only the digits of phone numbers (tutoring)
Phone numbers were typed with spaces, brackets, dots, dashes and plus signs. Keep just the digits.
- intermediate~9 minPro
Split a tag list into one row per tag (outdoor shop)
Each product has a semicolon-separated Tags cell. Produce one row per product and tag, trimmed, with no empty tags.
- intermediate~9 minPro
Split a tag list into one row per tag (recipes)
Each product has a semicolon-separated Tags cell. Produce one row per product and tag, trimmed, with no empty tags.
- intermediate~7 minPro
Build a one-line address that skips missing parts (subscribers)
Combine Line1, Line2, City and Postcode with ", " between them, leaving out Line2 when it's null.
- intermediate~7 minPro
Build a one-line address that skips missing parts (suppliers)
Combine Line1, Line2, City and Postcode with ", " between them, leaving out Line2 when it's null.