LEFT, RIGHT and MID in Google Sheets: extract part of a text
LEFT returns the first characters of a text, RIGHT the last, and MID a part from the middle. This guide shows the syntax of each, how to cut at a separator with FIND and LEN instead of a fixed number, how to extract first names, last names and the last word, and what the functions do with numbers and dates.
LEFT, RIGHT and MID return a part of a text, chosen by position:
| Function | Returns | Example | Result |
|---|---|---|---|
LEFT(text, [number]) |
The first characters | =LEFT("P123-WidgetA", 4) |
P123 |
RIGHT(text, [number]) |
The last characters | =RIGHT("P123-WidgetA", 7) |
WidgetA |
MID(text, start, length) |
Characters from the middle | =MID("123-456-7890", 5, 3) |
456 |
A fixed number of characters works when every value has the same layout. When the lengths differ, the position is calculated with FIND or SEARCH, as shown below.
Reading to get this done?
Syntax
=LEFT(string, [number_of_characters])
=RIGHT(string, [number_of_characters])
- string: the text, or a cell that holds it.
- number_of_characters: how many characters to return. Optional, 1 by default.
=LEFT(B3, 1)

=RIGHT(B3, 6)

=MID(string, starting_at, extract_length)
- starting_at: the position of the first character to return, counted from 1.
- extract_length: how many characters to return. All three arguments are required.
=MID(C3, 5, 3)

If the number is larger than what is left of the text, the functions return what there is: =LEFT("John", 100) returns John. This makes =MID(A2, 5, LEN(A2)) a safe way to say “from the fifth character to the end”.
Examples with a fixed number of characters
Examples with a fixed number of characters
Split a code into its parts
=LEFT(C3, 4)

=RIGHT(C3, 7)

The first digits of a phone number
=LEFT(C3, 3)

A whole column at once
=ARRAYFORMULA(LEFT(C3:C7, 4))

=ARRAYFORMULA(RIGHT(C3:C7, 2))

One formula in the first row fills the column. See array formulas.
Codes, names or SKUs sit glued in one cell, and the part you need starts at a different position in every row.
Paste this into your AI assistant. It reads your sheet's columns and writes the LEFT, RIGHT or MID formula with FIND that pulls out exactly the part you need.
The prompt
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /blog/articles/left-right-mid-functions-google-sheets.md. The task: split my SKU column into its parts with LEFT, MID and RIGHT, cutting at the dashes with FIND instead of fixed counts, and fill it down the whole column. Tag the links you give me with utm_campaign=ai-task and utm_term=m3. Use what you know about my context and goals
Cut at a separator: LEFT, RIGHT and MID with FIND
Cut at a separator: LEFT, RIGHT and MID with FIND
In “EMP456-JM” and “EMP1024-JM” the part before the dash has a different length, so a fixed number fails. FIND returns the position of the dash, and the rest is arithmetic.
Everything before the separator
=LEFT(C3, FIND("-", C3) - 1)

FIND returns 7 for “EMP456-JM”; one less is the number of characters before the dash.
Everything after the separator
=RIGHT(C3, LEN(C3) - FIND("-", C3))

LEN is the total length; minus the position of the dash, it is the number of characters after it.
SEARCH can replace FIND in both formulas. The only difference between them is that FIND is case-sensitive and SEARCH is not, which does not matter for a dash:
=LEFT(C3, SEARCH("-", C3) - 1)

=RIGHT(C3, LEN(C3) - SEARCH("-", C3))

Both find the first dash only, so the second formula returns everything after it, “456-7890”.
First and last name
=MID(B3, 1, FIND(" ", B3) - 1)

MID from position 1 is the same as LEFT: =LEFT(B3, FIND(" ", B3) - 1).
=MID(B3, SEARCH(" ", B3) + 1, LEN(B3))

The formula starts one character after the first space and runs to the end. With a middle name, it returns the middle and the last name together.
The last word
=TRIM(RIGHT(SUBSTITUTE(C3, " ", REPT(" ", 100)), 100))

SUBSTITUTE replaces every space with a hundred spaces, RIGHT takes the last hundred characters, which now hold only the last word and padding, and TRIM removes the padding. A full stop at the end of the sentence stays attached to the word.
When another function is simpler
For a text with a clear separator, these do the same job with less arithmetic:
=SPLIT(A2, "-")puts every part into its own cell. See how to split cells.=INDEX(SPLIT(A2, "-"), 1, 2)returns the second part only.=REGEXEXTRACT(A2, "\d+")returns the first run of digits. See REGEX functions.
Numbers and dates
The result is text
LEFT, RIGHT and MID always return text, also when the characters are digits. =RIGHT(C3, 4) on “15/01/2022” returns the text “2022”, which is left-aligned and is ignored by SUM. Convert it with VALUE:
=VALUE(RIGHT(C3, 4))

Dates stored as text
When a date is a text such as “2022/01/15”, its parts can be cut out by position:
=LEFT(C3, 4)

=RIGHT(D3, 4)

Real dates
On a cell that holds a real date, LEFT works on the date as it is displayed: with the format 1/5/2023, =LEFT(B3, 4) returns “1/5/”. The result changes with the cell format and the locale of the spreadsheet. Use the date functions instead: YEAR, MONTH and DAY, or =TEXT(B3, "yyyy") for a fixed layout.
Errors and wrong results
⚠️ Error: #VALUE!
✅ Solution: The number of characters is negative, or a nested FIND or SEARCH did not find its text. =LEFT(C3, FIND("-", C3) - 1) returns #VALUE! for a value without a dash. Wrap the formula in IFERROR to return the whole text in that case: =IFERROR(LEFT(C3, FIND("-", C3) - 1), C3).
⚠️ Error: #NUM! in MID.
✅ Solution: The start position is 0 or negative. Positions start at 1.
⚠️ Error: MID returns an empty cell.
✅ Solution: The start position is past the end of the text.
⚠️ Error: One character too many or too few.
✅ Solution: Check the +1 and -1 around FIND, and look for spaces at the start or the end of the cell: they count as characters. Clean the text with TRIM first.
⚠️ Error: The extracted number cannot be summed or sorted as a number.
✅ Solution: The result is text. Wrap the formula in VALUE.
⚠️ Error: A lookup on the extracted value fails.
✅ Solution: For the same reason: the extracted “123” is text and the key in the other table is a number. Convert one side, with VALUE or with TEXT.
Related Google Sheets functions
Related Google Sheets functions
- FIND and SEARCH: The position of a text inside another.
- LEN: The length of a text.
- How to split cells: SPLIT and Split text to columns.
- REGEX functions: Extract by pattern.
- SUBSTITUTE and REPLACE: Change a part of a text.
- TRIM, CLEAN and T: Remove extra spaces.
- CONCATENATE and the ampersand: Join the parts back together.
- UPPER, LOWER and PROPER: Change the case.
Parsing codes in the sheet is a workaround
Parsing codes in the sheet is a workaround
Cutting a category out of a product ID or a year out of a text date is a sign that the column was delivered in the wrong shape. The formula fixes it for this export; the next export needs the same formulas again, and a new code format breaks them without warning, because LEFT returns four characters whatever they are.
The OWOX Data Marts extension for Google Sheets covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas.

What changes for the person who maintains the sheet:
- Refresh replaces re-export. Refresh current report in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it.
- The source is written on the sheet. The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report.
- No SQL and no warehouse access needed. The analyst decides which data marts and columns are available. You choose from that list.
The extension does not write or change the analyst’s SQL. When the split is done once in that SQL, the category and the year arrive as columns of their own, and LEFT, RIGHT and MID are left for the occasional one-off. The extension comes with the OWOX Cloud plans of OWOX Data Marts; spreadsheet reporting describes both ways data reaches a sheet.
Codes, names or SKUs sit glued in one cell, and the part you need starts at a different position in every row.
Paste this into your AI assistant. It reads your sheet's columns and writes the LEFT, RIGHT or MID formula with FIND that pulls out exactly the part you need.
The prompt
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /blog/articles/left-right-mid-functions-google-sheets.md. The task: split my SKU column into its parts with LEFT, MID and RIGHT, cutting at the dashes with FIND instead of fixed counts, and fill it down the whole column. Tag the links you give me with utm_campaign=ai-task and utm_term=m3. Use what you know about my context and goals
Prefer to start on your own? Get started free
