FormuladrillLEN, LEFT, RIGHT, MID
LEFT(text, n) returns the first n characters, RIGHT(text, n) the last n, MID(text, start, n) n characters from a position, and LEN(text) the number of characters. Combine them with FIND or SEARCH to extract the part before or after a separator, such as the domain of an email address.
Syntax
LEFT(text, [num_chars]) RIGHT(text, [num_chars]) MID(text, start_num, num_chars) LEN(text)| Part | What it is |
|---|---|
text | The cell or string to work on. |
num_chars | How many characters to return. Defaults to 1 for LEFT and RIGHT. |
start_num (MID) | Position of the first character to return, counted from 1. |
Examples
=LEFT("ABC-123", 3)Result: ABC
=RIGHT("ABC-123", 3)Result: 123
The result is text; wrap it in VALUE if you need a number.
=MID("ABC-123", 5, 2)Result: 12
Start at character 5, take 2.
=MID(A2, FIND("@", A2) + 1, LEN(A2))Result: example.com when A2 is jane@example.com
Everything after the @. LEFT(A2, FIND("@", A2) - 1) gives the part before it.
Common mistakes
- #VALUE! from FIND: the character was not found. Use SEARCH for case-insensitive matching, or wrap in IFERROR.
- LEN counts spaces, including trailing ones you cannot see; TRIM first if lengths look wrong.
- RIGHT(A2, 3) on a number returns text, so 123 no longer adds up. Use VALUE(RIGHT(A2, 3)).
- MID with a start beyond the text length returns an empty string, not an error, which can hide a problem.
Practice LEN, LEFT, RIGHT, MID
These drills check your answer on live data, free, no account needed.
- Pull out the domainExtract everything after the @ sign from each email address. Fill B2 and B3.
- Build an email addressCreate each email address from the names: the first letter of the first name, then the last name, all lowercase, then @acme.com. For example Maria Lopez becomes mlopez@acme.com. Fill C2 and C3.
Questions
How do I extract text before a space or comma?
=LEFT(A2, FIND(" ", A2) - 1) for the first word; replace the space with "," for everything before a comma.
How do I extract text after a character?
=MID(A2, FIND("-", A2) + 1, LEN(A2)). Giving LEN(A2) as the length simply takes the rest of the string. In Microsoft 365, TEXTAFTER(A2, "-") does the same.
How do I get the last word in a cell?
=TRIM(RIGHT(SUBSTITUTE(A2, " ", REPT(" ", 100)), 100)), or TEXTAFTER(A2, " ", -1) in Microsoft 365.
Related
- SUBSTITUTEHow the Excel SUBSTITUTE function replaces one piece of text with another, with examples for removing dashes, swapping the nth occurrence and counting commas.
- TRIMHow the Excel TRIM function removes leading, trailing and repeated spaces, why lookups fail without it, and how to remove the non-breaking spaces it leaves.
- CONCAT and &How to join text in Excel with CONCAT, CONCATENATE and the & operator, with examples for full names, email addresses, numbered IDs and a formatted date.
- TEXTHow the Excel TEXT function turns a number or date into text in a format you choose, with examples for dates, thousand separators, percentages and padding.