LEFT, RIGHT and MID in Excel: Pull Out Part of a Cell

Excel LEFT, RIGHT and MID pull out part of a cell: LEFT takes characters from the start, RIGHT from the end, and MID from the middle. They're how you split a product code, an employee ID or an account number into its parts without retyping anything. This guide is for beginners and job seekers, and it uses one set of product codes so you can see all three side by side.

What do LEFT, RIGHT and MID do?#

All three functions return part of a piece of text. Each character counts: letters, digits, spaces and symbols like the dash. Here is the syntax:

text is usually a cell. num_chars is how many characters to take; for LEFT and RIGHT it's optional and defaults to 1. start_num is the position of the first character you want, counting from 1 at the left. These functions work in every version of Excel.

The examples use these product codes in column A. Each one is a 2-letter region, a dash, a 3-letter category, a dash and a 5-digit item number:

How to use LEFT to take characters from the start#

LEFT takes a number of characters from the beginning of the text. The region is the first 2 characters, so in B2:

=LEFT(A2,2)

The result is TX. Select B2:B7 and fill down (Windows: Ctrl+D; Mac: ⌘D) to get CA, NY, WA, FL and IL. If you leave out the 2, =LEFT(A2) returns just T.

How to use RIGHT to take characters from the end#

RIGHT works the same way, counting back from the end of the text. The item number is the last 5 characters, so in D2:

=RIGHT(A2,5)

The result is 40512. RIGHT doesn't care how long the rest of the text is; it always counts from the right.

How to use MID to take characters from the middle#

MID needs a start position as well as a length, because the part you want isn't at either end. Count along TX-ELC-40512: T is 1, X is 2, the dash is 3, and E is 4. The category starts at 4 and is 3 characters long, so in C2:

=MID(A2,4,3)

The result is ELC. A common mistake is starting at 3, which gives -EL, because the dash counts.

MID is forgiving at the end of the text. If num_chars asks for more characters than are left, MID returns what's there, so =MID(A2,8,100) gives 40512. If start_num is past the end, MID returns empty text.

Why does RIGHT return a number I can't add up?#

LEFT, RIGHT and MID always return text, even when the result looks like a number. SUM skips text, and lookups won't match the text "40512" to the number 40512. You can often spot it: text sits on the left of the cell, numbers on the right.

To turn the result into a real number, wrap it in VALUE:

=VALUE(RIGHT(A2,5))

Excel works from the inside out: RIGHT returns the text "40512", then VALUE turns it into the number 40512. With a code like SKU-0042, =VALUE(RIGHT(A2,4)) gives 42, because a number has no leading zeros. Keep the text version if the zeros matter, as with ZIP codes or account numbers.

How to handle text of different lengths with LEN#

When the part you want changes length, use LEN to measure the text. =LEN(text) counts every character. Take these employee IDs:

Every ID starts with the same 4 characters, EMP-. So the number is the last LEN(A2) minus 4 characters:

=RIGHT(A2,LEN(A2)-4)

For EMP-1204, LEN gives 8, minus 4 is 4, and RIGHT takes 1204. Putting one function inside another like this is called nesting.

When there's no fixed prefix, look for a marker instead, such as a space or an @. FIND returns its position, and LEFT or MID can use that. For example, =LEFT(A2,FIND(" ",A2)-1) returns everything before the first space. See how to find the position of text with FIND and SEARCH for the full method.

Common mistakes with LEFT, RIGHT and MID#

Most wrong results come from miscounting or from cells that aren't quite what they seem:

1. Miscounting positions. Spaces, dashes and dots are characters. Count carefully, or check with LEN.
2. Hidden spaces. A cell like " TX-ELC-40512" with a leading space shifts every position by one. Clean it first with TRIM; see how to clean messy data with TRIM, CLEAN and PROPER.
3. Using them on dates. A date is stored as a number, so =LEFT(A2,4) on Feb 3, 2025 returns 4569, the first digits of 45691. Use =YEAR(A2) or =TEXT(A2,"yyyy") instead.
4. Using them on numbers. =LEFT(2025,2) returns the text "20". Wrap it in VALUE if you need a number.
5. Swapping MID's arguments. The order is text, start, length. =MID(A2,3,4) and =MID(A2,4,3) give different results.

What to use instead: TEXTBEFORE, TEXTAFTER and Flash Fill#

In Microsoft 365, TEXTBEFORE and TEXTAFTER split text at a delimiter with no counting: =TEXTBEFORE(A2,"-") gives TX. They're often simpler than LEFT with FIND, but they don't exist in Excel 2021 and older. For a one-off job, Flash Fill (Windows: Ctrl+E; Mac: Data > Flash Fill) copies a pattern you type by example, though it doesn't update when the data changes.

To put the pieces back together in a new order, use & or TEXTJOIN; see how to combine text in Excel. The LEFT function guide is a quick syntax reference.

Practise it#

Practise LEFT, RIGHT, MID and LEN in a live spreadsheet. You'll split product codes into region, category and item number, turn text digits into real numbers with VALUE, and use LEN to handle IDs of different lengths.