Excel Text Functions Practice: TRIM, LEFT, TEXT and More
Real data is rarely tidy: names typed in capitals with extra spaces, codes that need splitting, amounts imported as text that Excel refuses to add. Text functions let a formula clean, split, join and format text so you never retype it by hand.
These exercises build the skills in order: cleaning and combining names, taking characters from the start or end of a code, finding a separator to split an email address, turning numbers and dates into formatted text for a caption, and converting messy imported text back into real numbers. Each is checked on hidden data, so a formula that only works for the text you can see does not pass.
What these functions are for
- Remove extra spaces and fix capital letters in names (TRIM, PROPER).
- Take part of a code or address from the left, the right or around a separator (LEFT, RIGHT, FIND).
- Show numbers, percentages and dates inside a sentence (TEXT).
- Turn imported text such as "$1,250" into a number you can add (SUBSTITUTE, VALUE).
Tips before you start
- Functions can be nested: read the formula from the inside out, because the inner result feeds the outer function.
- LEFT, RIGHT and MID return text, even when it looks like a number.
- Find a separator with FIND instead of counting characters, so any length of text works.
- Keep the original number for calculations; use TEXT only for the version people read.
Practice exercises, in a sensible order
Clean and Combine Names with TRIM and PROPER
Clean imported text with TRIM and PROPER and combine pieces of text into one result.
Split Product Codes with LEFT, RIGHT and LEN
Take characters from the start or end of a text value with LEFT and RIGHT, and measure it with LEN.
Split Email Addresses into Username and Domain
Extract part of a text by finding a separator, instead of counting characters by hand.
Format Numbers and Dates as Text with TEXT
Convert numbers, percentages and dates to formatted text with TEXT, and join them with other text using &.
Clean Imported Amounts with TRIM, SUBSTITUTE and VALUE
Combine TRIM, SUBSTITUTE and VALUE to turn imported text into numbers that can be summed.