Text function essentials
Parse, clean, and combine text using the core text-manipulation formulas Excel users rely on every day.
Exercises in this track
CONCATENATE names
Combine first name and last name columns into a full name.
Combine text with CONCATENATE
Combine first and last names into full names using the CONCATENATE function.
Extract department code with LEFT
Extract the 3-letter department code from the start of each SKU (e.g. FIN-1042 -> FIN).
Fixing text to be exact
The EXACT function in Excel is pretty simple. It returns TRUE when the two given arguments are exactly the same. Otherwise, it returns FALSE. We've...
The Substitute function
You can use the Substitute function for all kinds of textual replacing. Try changing the X in cell A2 below into a 1 by using SUBSTITUTE in cell B2....
Clean phone numbers
Standardize phone numbers by removing parentheses, dashes, and spaces.
Extract domain from email
Extract the domain name (after @) from email addresses.
Parse full address
Extract city from a full address string (assume consistent format).
Extract file extension from a path
Extract file extensions from shared-drive file paths when the number of characters after the dot changes from row to row.
Clean and standardize names
Standardize company names (remove Inc/LLC/Corp, handle "The", title case).
Complex text parsing
Parse inconsistently formatted addresses into separate columns (street, city, state, zip).