Text functions in Excel let you pull out or clean up parts of text. LEFT takes characters from the start of a cell, RIGHT from the end, and MID from the middle. LEN counts how many characters are in a cell, and TRIM removes extra spaces. These are the tools you use to split codes, extract names, and tidy up messy imported data.
LEFT, RIGHT and MID
=LEFT(text, number_of_characters)
=RIGHT(text, number_of_characters)
=MID(text, start_position, number_of_characters)
=LEFT("INV2026",3)→INV(first 3 characters).=RIGHT("INV2026",4)→2026(last 4 characters).=MID("INV2026",4,4)→2026(4 characters starting at position 4).
LEN and TRIM
=LEN(A2)counts the characters, including spaces. Useful to check code lengths.=TRIM(A2)removes extra spaces (leading, trailing, and doubles between words). This fixes lookups that fail because of stray spaces.
Joining text: & and CONCAT
To combine text from cells, use & or CONCAT:
=A2&" "&B2 joins first and last name with a space
=CONCAT(A2," ",B2) same result
If A2 is "Rahul" and B2 is "Verma", both give "Rahul Verma".
Changing case: UPPER, LOWER, PROPER
=UPPER(A2)→ all capitals.=LOWER(A2)→ all small letters.=PROPER(A2)→ First Letter Of Each Word Capital.
Great for cleaning names that were typed inconsistently.
Quick reference
| Function | Does | Example result |
|---|---|---|
LEFT | Characters from start | LEFT("ABCDE",2) → AB |
RIGHT | Characters from end | RIGHT("ABCDE",2) → DE |
MID | Characters from middle | MID("ABCDE",2,3) → BCD |
LEN | Count characters | LEN("ABC") → 3 |
TRIM | Remove extra spaces | TRIM(" A B ") → "A B" |
PROPER | Capitalise each word | PROPER("rahul verma") → Rahul Verma |
Worked example (split a product code)
Codes look like MUM-1024, where the first 3 letters are the city and the last 4 are the ID.
- City:
=LEFT(A2,3)→MUM - ID:
=RIGHT(A2,4)→1024
Now you can sort or filter by city, which you couldn't do when it was one column.
Pro tips
- Wrap a lookup value in
TRIM()if a VLOOKUP keeps failing; stray spaces are a common cause. - Combine functions:
=PROPER(TRIM(A2))cleans spaces and fixes capitals in one go. - Use LEN to check that every code is the right length before you process it.
Common mistakes
- Counting positions wrong in MID. The start position is 1-based: the first character is position 1, not 0.
- Forgetting spaces when joining.
=A2&B2gives "RahulVerma". Add&" "&for a space. - Extra spaces breaking lookups. Use TRIM to clean imported data.
Key takeaways
- LEFT, RIGHT and MID extract characters from the start, end and middle.
- LEN counts characters; TRIM removes extra spaces.
- Join text with
&(add" "for spaces) or CONCAT. - UPPER/LOWER/PROPER fix the case of text.
Practice task
Make a column of codes like DEL-2050. Use LEFT to pull the city, RIGHT to pull the ID, and PROPER to tidy a column of lowercase names. Check LEN to confirm each code has 8 characters.
Learn the full range of Excel functions employers want in the ADCA program at HCI.
Frequently Asked Questions
What are text functions in Excel?
They extract or clean parts of text. LEFT, RIGHT and MID pull characters; LEN counts them; TRIM removes extra spaces; and PROPER/UPPER/LOWER fix the case.
What is the difference between LEFT, RIGHT and MID?
LEFT takes characters from the start, RIGHT from the end, and MID from a chosen start position in the middle of the text.
How do I join two cells of text in Excel?
Use `=A2&" "&B2` or `=CONCAT(A2," ",B2)`. The `" "` adds a space between the two values.
How do I remove extra spaces in Excel?
Use the TRIM function: `=TRIM(A2)` removes leading, trailing and double spaces. It's useful for cleaning imported data.
How do I capitalise the first letter of each word?
Use PROPER: `=PROPER(A2)` capitalises the first letter of each word, which is handy for tidying names.