=LEFT(A2,3) returns the first 3 characters of the text in A2, =RIGHT(A2,1) the last character, and =MID(A2,5,4) returns 4 characters starting at the 5th. For the code NYC-2041-B that is NYC, B and 2041.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Code | City | Number | Size |
| 2 | NYC-2041-B | NYC | 2041 | B |
| 3 | LAX-1187-A | LAX | 1187 | A |
| 4 | SFO-3302-C | SFO | 3302 | C |
| 5 | BOS-0915-B | BOS | 0915 | B |
Click C2 and change the 5 to 4: the result now starts at the dash. Spaces and punctuation count as characters, and the first character is position 1.
Syntax
=LEFT(text, [num_chars])
=RIGHT(text, [num_chars])
=MID(text, start_num, num_chars)
num_charsis optional in LEFT and RIGHT and defaults to 1.- If you ask for more characters than there are, you get the whole text, not an error.
=LEFT("Ann",10)isAnn. - A negative count gives #VALUE!, for example
=LEFT(A2,LEN(A2)-3)on a text shorter than 3 characters.
When the length varies: LEFT, RIGHT and MID with FIND
Codes are rarely all the same length. Use FIND to locate the separator and let the formula work out the count:
| A | B | C | |
|---|---|---|---|
| 1 | Code | Before the dash | After the dash |
| 2 | NY-2041 | NY | 2041 |
| 3 | BOSTON-15 | BOSTON | 15 |
| 4 | LA-30087 | LA | 30087 |
FIND("-",A2)returns the position of the dash, 3 inNY-2041.LEFT(A2,3-1)takes the 2 characters before it.MID(A2,3+1,50)starts after the dash and takes up to 50 characters, more than any code has, so it reaches the end.
In Microsoft 365 and Excel 2024, TEXTBEFORE(A2,"-") and TEXTAFTER(A2,"-") give the same results in a shorter formula; see splitting text.
Remove the first or last characters
To drop characters, keep the rest. LEN gives the total length, and you subtract what you want to remove:
| A | B | C | |
|---|---|---|---|
| 1 | Text | Without first 3 | Without last 2 |
| 2 | ID-58210 | 58210 | ID-582 |
| 3 | ID-7734 | 7734 | ID-77 |
| 4 | ID-120 | 120 | ID-1 |
| A | B | |
|---|---|---|
| 1 | Card | Last four |
| 2 | 4111 2222 3333 9876 |
Your turn: In B2, return the last 4 characters of the card number in A2.
Extract a number from a code
LEFT, RIGHT and MID always return text, even when the characters are digits. Text digits look like numbers but SUM skips them, which is why a total of extracted numbers can come out as 0:
| A | B | C | |
|---|---|---|---|
| 1 | Order | MID (text) | VALUE(MID) |
| 2 | SO-0120-X | 0120 | 120 |
| 3 | SO-0345-Y | 0345 | 345 |
| 4 | SO-1000-Z | 1000 | 1000 |
| 5 | Total | 0 | 1465 |
Column B shows 0120 with its leading zero because it is still text, and B5 totals 0. Column C converts each piece with VALUE and the total is right. =--MID(A2,4,4) and =MID(A2,4,4)*1 convert the same way; see text to number for more.
| A | B | |
|---|---|---|
| 1 | Item | Quantity |
| 2 | PK-045-BAG |
Your turn: The quantity sits between the two dashes and always has 3 digits. In B2, extract it as a number you can add up.
Common mistake: LEFT on a date
A date is stored as a number, so =LEFT(B2,4) on the date 2026-03-15 does not return 2026: it returns 4609, the first 4 digits of 46096, the serial number behind the date.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Event | Date | LEFT(date,4) | Year |
| 2 | Launch | 2026-03-15 | 4609 | 2026 |
| 3 | 2026 |
Use YEAR(B2) for the year as a number, or TEXT(B2,"yyyy") for the year as text. MONTH, DAY and TEXT with "mmm" or "dd" cover the other parts.
Frequently Asked Questions
How do I extract the first N characters in Excel?
Use LEFT with the count: =LEFT(A2,3) returns the first 3 characters. Without the second argument, =LEFT(A2) returns only the first character.
How do I remove the first or last characters in Excel?
Keep the rest with LEN: =RIGHT(A2,LEN(A2)-3) removes the first 3 characters and =LEFT(A2,LEN(A2)-2) removes the last 2.
Why can't I add up the numbers MID returns?
LEFT, RIGHT and MID always return text, and SUM skips text. Turn the result into a number with VALUE or two minus signs: =VALUE(MID(A2,5,4)) or =--MID(A2,5,4).
How do I get the text before a character in Excel?
Find the character's position and take one less: =LEFT(A2,FIND("-",A2)-1) returns everything before the first dash. In Microsoft 365, =TEXTBEFORE(A2,"-") does the same.
