Theory
The messy imported list
Aryan imports a customer list and it is a mess: names have extra spaces (' Riya Shah '), phone numbers are glued to area codes, and he needs a product code made of the first three letters of each item in capitals.
Retyping thousands of rows is out of the question.
Excel's text functions are surgical tools for strings: pull out a piece, measure a length, glue values together, strip stray spaces. This is the same string-handling you met with C char arrays (BCA104) and Text-to-Columns (BCA105), now as precise, reusable formulas.
Theory
Scissors, ruler, and glue
Working with text is like a craft desk: scissors to cut out a piece (LEFT, RIGHT, MID), a ruler to measure length (LEN), and glue to join pieces together (CONCAT, or the & operator). Add a cleaning cloth (TRIM) to wipe off stray spaces. With these, Aryan can reshape any string, extract, measure, combine, without ever retyping. Text functions are the craft desk for data.
At a glance
The text toolkit
| Function | Does | Example -> result |
|---|---|---|
| LEFT(text, n) | First n characters | LEFT("Grocery",3) -> Gro |
| RIGHT(text, n) | Last n characters | RIGHT("9876",4) -> 9876 |
| MID(text, s, n) | n chars from position s | MID("Sugar",2,3) -> uga |
| LEN(text) | Length (character count) | LEN("Tea") -> 3 |
| CONCAT / & | Join strings | "Ri"&"ya" -> Riya |
| TRIM(text) | Remove extra spaces | TRIM(" a b ") -> a b |
Theory
Combine them for real jobs
The power comes from nesting these functions:
- Product code:
=UPPER(LEFT(A2, 3))-> first 3 letters, uppercased ('Grocery' -> 'GRO'). - Join a full name:
=B2 & " " & C2-> 'Riya' + space + 'Shah' -> 'Riya Shah'. - Clean then use:
=TRIM(A2)strips the stray spaces before anything else. - Extract before a space (split a name): combine FIND (locate the space position) with LEFT/MID.
Positions are 1-indexed (the first character is position 1), the same counting to watch as in any string work.
Quiz
What does =MID("Rajkot", 2, 3) return?
- ajk, 3 characters starting from position 2
- Raj, the first 3 characters
- kot, the last 3 characters
- Rajkot, the whole word
Show the answer
ajk, 3 characters starting from position 2
MID(text, start, n) takes n characters starting from position start. Starting at position 2 of 'Rajkot' (the 'a') and taking 3 gives 'ajk'. LEFT would give the first 3 ('Raj'), RIGHT the last 3 ('kot'). MID is the flexible middle-extractor, and its 1-indexed start position is the detail exams test.
Think first
Why TRIM before comparing?
Aryan tries to match customer 'Riya Shah' against a lookup list, but it keeps failing even though the name is there. He suspects the imported name is ' Riya Shah ' with hidden spaces. Which function fixes it, and why do the spaces matter?
Show the answer
TRIM(name) removes leading, trailing and extra internal spaces. The spaces matter because to a computer, ' Riya Shah ' and 'Riya Shah' are different strings, so an exact match (or VLOOKUP) fails. Stray spaces from imports are a top cause of 'the data is right but the lookup fails'. Cleaning with TRIM before matching is a standard first step, echoing the messy-data problems from BCA105.
Watch out
Where marks leak
Mixing up LEFT (from the start), RIGHT (from the end) and MID (from a position). Forgetting positions are 1-indexed (first char = 1). Not knowing TRIM removes stray spaces (a huge real-world gotcha) or LEN counts characters. And forgetting the `&` operator joins strings just like CONCAT. Combine functions by nesting, and clean with TRIM before any matching.
Theory
Clean data makes lookups work
Text functions are the cleanup crew that makes everything downstream reliable, especially VLOOKUP, which fails on the tiniest text mismatch (a stray space, wrong case). Master TRIM and the extractors, and your lookups stop mysteriously breaking. Speaking of which, the star of this elective is next: VLOOKUP, HLOOKUP and XLOOKUP, the functions that fetch a value from another table by matching a key.
Summary
Key takeaways
- LEFT(text,n), RIGHT(text,n), MID(text,start,n) extract pieces by position (1-indexed).
- LEN measures character count; CONCAT or the & operator joins strings.
- TRIM removes stray spaces (a top cause of failed matches); UPPER/LOWER/PROPER re-case.
- FIND/SEARCH locate a character's position, useful for splitting at a space.
- Nest functions for real jobs: =UPPER(LEFT(A2,3)) builds a product code.
- Memory hook: scissors (LEFT/RIGHT/MID), ruler (LEN), glue (& / CONCAT), cloth (TRIM).