LEFT, RIGHT, and MID Generator
Pull a set number of characters out of a cell from the start, the end, or any position.
LEFT RIGHT MID
Pull a set number of characters out of a cell from the start, the end, or any position.
The cell to extract characters from.
Which character to start at, counting from 1 for the first character. Required.
How many characters to return from the start position onward. Required for MID, optional for LEFT and RIGHT.
=MID(A2, 5, 3)Worked Example
A2 contains the product code SKU-123-RED and you want the three-digit number in the middle.
=MID(A2, 5, 3)Returns: Returns 123 as text, starting at the fifth character. Wrap it in VALUE if you need a real number.
Checks Before You Paste
- •MID counts from 1, not 0. A start_num below 1, or a negative num_chars, returns #VALUE!, while a num_chars that runs past the end of the text simply returns whatever is left with no error.
- •Swap to LEFT(text, [num_chars]) to take characters from the start and RIGHT(text, [num_chars]) to take them from the end. Both take num_chars as an optional second argument that defaults to 1; MID needs all three arguments.
- •When the position varies, feed it from FIND or SEARCH, for example =MID(A2, FIND("-", A2)+1, 3). FIND is case sensitive and SEARCH is not, and SEARCH also accepts the wildcards * and ?.
How LEFT RIGHT MID works
Availability: LEFT, RIGHT and MID are in every version of Excel and in Google Sheets, as are their partners FIND, SEARCH and LEN. The LEFTB, RIGHTB and MIDB byte-counting variants exist for double-byte languages. Microsoft 365 adds TEXTBEFORE and TEXTAFTER, which handle most delimiter jobs in one argument instead of a nested FIND, but they are not available in Excel 2021 or earlier.
These three functions cut a fixed number of characters out of a string. LEFT takes them from the start, RIGHT from the end, and MID from any position you name. Between them they cover almost every fixed-format extraction: a country code off the front of a phone number, a file extension off the end of a path, the branch code buried in the middle of an account reference.
MID counts positions from 1, not 0, so the first character is position 1 and MID(A2, 5, 3) starts at the fifth. Its third argument is required; LEFT and RIGHT take theirs optionally and default to 1, which is why =LEFT(A2) returns a single character rather than the whole string. Asking for more characters than remain is not an error - MID(A2, 5, 100) simply returns whatever is there from position five onward, which makes it a convenient way to say "everything after this point".
The functions become genuinely useful once the position is calculated rather than typed. FIND and SEARCH return the position of a substring, so =MID(A2, FIND("@", A2)+1, LEN(A2)) pulls the domain out of any email address regardless of how long the name is. FIND is case sensitive and takes no wildcards; SEARCH ignores case and accepts * and ?. Both return #VALUE! when the target is not there, so an IFERROR wrapper is usually warranted on real data.
Everything these functions return is text. That is fine for codes and references, and a problem for anything you then want to add up or compare as a date - wrap the result in VALUE or DATEVALUE. It also means they are the wrong tool for splitting dates: LEFT on a date cell operates on the serial number underneath, not on what the cell displays.
Syntax
=MID(text, start_num, num_chars), with =LEFT(text, [num_chars]) and =RIGHT(text, [num_chars])| Argument | Required | What it does |
|---|---|---|
text | Required | The string to take characters from - a cell reference, quoted literal text, or another formula's result. Numbers, dates and TRUE/FALSE are converted to text first, using the underlying value rather than the displayed format. |
start_num | Required | MID only. The position of the first character to return, counting from 1. Values below 1 return #VALUE!; a value beyond the end of the string returns an empty string rather than an error. |
num_chars | Required | How many characters to return. Required for MID. For LEFT and RIGHT it is optional and defaults to 1, so =RIGHT(A2) gives just the final character. Zero returns an empty string; a negative value returns #VALUE!; a value larger than what remains returns everything that is left, with no error and no padding. |
More worked examples
A2 holds the UK phone number 0161 496 0123 as text, and the report needs the area code on its own.
=LEFT(A2, 4)Returns: 0161
The leading zero survives because the result is text. Wrapping this in VALUE would turn it into 161 and lose the thing that makes it an area code.
A2 holds the file name quarterly-report.final.xlsx and you need the extension, knowing there can be any number of dots in the name.
=MID(A2, FIND(CHAR(1), SUBSTITUTE(A2, ".", CHAR(1), LEN(A2)-LEN(SUBSTITUTE(A2, ".", ""))))+1, LEN(A2))Returns: xlsx
LEN(A2)-LEN(SUBSTITUTE(A2,".","")) counts the dots - two here - so SUBSTITUTE swaps the last one for CHAR(1), a control character no filename contains. FIND then has a unique target to locate. In Microsoft 365 the same result is just =TEXTAFTER(A2, ".", -1).
A2 holds the email address dana@northwind.co.uk and the mailing list needs the domain in its own column.
=MID(A2, FIND("@", A2)+1, LEN(A2))Returns: northwind.co.uk
Passing LEN(A2) as num_chars is a deliberate over-request: it is always at least as long as the remainder, and MID returns what exists rather than erroring. If a row might have no @ at all, wrap the whole thing in IFERROR.
Common mistakes
- LEFT on a date returns digits of the serial number
- A2 showing 01/03/2026 actually holds 46082, so =LEFT(A2, 2) returns 46, not 01. Convert the display first with =LEFT(TEXT(A2, "dd/mm/yyyy"), 2), or - better - use the date functions that exist for this: DAY(A2), MONTH(A2), YEAR(A2).
- Leading zeros vanish before the formula even runs
- A product code entered as 00421 in a General cell is stored as the number 421, so =LEFT(A2, 3) gives 421 rather than 004. Either store such codes as text from the start, or rebuild the padding inside the formula with =LEFT(TEXT(A2, "00000"), 3).
- FIND fails on a case mismatch and returns #VALUE!
- =MID(A2, FIND("id-", A2)+3, 6) errors on a row where the text reads ID-123456, because FIND is case sensitive. Swap to SEARCH, which is not, or normalise with FIND("ID-", UPPER(A2)). Either way wrap the formula in IFERROR so a single bad row does not fill the column with errors.
- SEARCH treats * and ? as wildcards
- Looking for a literal asterisk with =SEARCH("*", A2) matches at position 1 on every row, because * means any run of characters. Escape it with a tilde - SEARCH("~*", A2) - or use FIND, which has no wildcard behaviour at all.
- Extracted numbers do not add up
- =MID(A2, 5, 3) on SKU-123-RED returns the text "123", which SUM ignores and which sorts before "9". Wrap it as =VALUE(MID(A2, 5, 3)). Note that VALUE returns #VALUE! if the extracted characters are not numeric, so on mixed data use =IFERROR(VALUE(MID(A2, 5, 3)), "").
Frequently Asked Questions
None of them on their own - all three need a number, and a moving target means the number has to be calculated. Locate the delimiter with FIND or SEARCH and feed the position in: MID for something bounded by two delimiters, RIGHT with LEN minus the delimiter position for everything after one, LEFT with the position minus one for everything before it.
Count the delimiters with LEN(A2)-LEN(SUBSTITUTE(A2, "-", "")), use that count as SUBSTITUTE's instance_num to swap the final one for a character that cannot occur in the data such as CHAR(1), then FIND that marker and take everything after it with MID. In Microsoft 365, =TEXTAFTER(A2, "-", -1) does the same job in one function.
Because num_chars is optional for LEFT and RIGHT and defaults to 1. It is only MID that insists on all three arguments. If you meant the whole string, LEFT is not the function you want - a plain =A2 or =TEXT(A2, "@") does that.
Yes, every character counts, including spaces at the ends of the string and non-breaking spaces pasted in from a web page. That is why extraction positions calculated on clean sample data drift by one or two on imported rows. Wrap the source in TRIM before extracting, and SUBSTITUTE out CHAR(160) if the data came from a browser.
Usually yes. TEXTBEFORE and TEXTAFTER take the delimiter directly and accept a negative instance number to count from the right, and TEXTSPLIT breaks a string into columns in one go. They only exist in Microsoft 365 and Excel for the web, so a workbook that has to open in Excel 2021 or earlier still needs the LEFT/RIGHT/MID construction.
Related Tools
TEXTSPLIT Generator
Break a string into columns by delimiter instead of counting character positions.
Text to Columns
Split a column in an uploaded workbook without writing an extraction formula.
SUBSTITUTE Generator
Mark the nth delimiter so FIND can locate it for a RIGHT or MID extraction.
Excel Column Extractor
Keep, drop or reorder whole columns once the pieces are in place.