Google Sheets Tips

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.

Google Sheets TipsVadym Kramarenko8 min read

LEFT, RIGHT and MID in Google Sheets: extract part of a text

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

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)

Google Sheets with the name James Miller in B3 and the formula =LEFT(B3,1) returning J

=RIGHT(B3, 6)

Google Sheets with the name James Miller in B3 and the formula =RIGHT(B3, 6) returning Miller

=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)

Google Sheets with phone numbers in column C and the formula =MID(C3, 5, 3) returning 456 from 123-456-7890

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)

Google Sheets with codes such as P123-WidgetA in column C and the formula =LEFT(C3,4) returning P123

=RIGHT(C3, 7)

Google Sheets with the same codes and the formula =RIGHT(C3,7) returning WidgetA

The first digits of a phone number

=LEFT(C3, 3)

Google Sheets with phone numbers in column C and the formula =LEFT(C3,3) returning the first three digits, 415

A whole column at once

=ARRAYFORMULA(LEFT(C3:C7, 4))

Google Sheets with employee IDs in C3:C7 and the formula =ARRAYFORMULA(LEFT(C3:C7, 4)) returning the first four characters of each

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

Google Sheets with the formula =ARRAYFORMULA(RIGHT(C3:C7, 2)) returning the last two characters of each employee ID

One formula in the first row fills the column. See array formulas.

Hand this to your AI

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

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

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)

Google Sheets with employee IDs such as EMP456-JM in column C and the formula =LEFT(C3, FIND(“-”, C3)-1) returning EMP456

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))

Google Sheets with the formula =RIGHT(C3, LEN(C3) - FIND(“-”, C3)) returning JM from EMP456-JM

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)

Google Sheets with phone numbers in column C and the formula =LEFT(C3, SEARCH(“-”, C3)-1) returning 123 from 123-456-7890

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

Google Sheets with the formula =RIGHT(C3, LEN(C3) - SEARCH(“-”, C3)) returning 456-7890 from 123-456-7890

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)

Google Sheets with full names in column B and the formula =MID(B3, 1, FIND(” “, B3) - 1) returning the first names

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

=MID(B3, SEARCH(" ", B3) + 1, LEN(B3))

Google Sheets with full names in column B and the formula =MID(B3, SEARCH(” “, B3) + 1, LEN(B3)) returning the last names

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))

Google Sheets with sentences in column C and a formula built from TRIM, RIGHT, SUBSTITUTE and REPT returning the last word of each, such as satisfaction

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

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))

Google Sheets with joining dates stored as text in column C and the formula =VALUE(RIGHT(C3, 4)) returning the year as a number

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)

Google Sheets with dates written as text in two layouts and the formula =LEFT(C3, 4) returning the year from 2022/01/15

=RIGHT(D3, 4)

Google Sheets with the formula =RIGHT(D3, 4) returning the year from a text date written as 15/01/2022

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

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

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.

Google Sheets with the OWOX Data Marts extension open beside an ad spend table: the Unified Ad Spend data mart, nine of fourteen columns ticked and a Save & Run button – the rows in the sheet come from a governed data mart, not a pasted export

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.

Hand this to your AI

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

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

Who wrote this

Vadym Kramarenko

Vadym Kramarenko Growth Marketing Manager

Vadym Kramarenko is a Growth Marketing Manager at OWOX, where he drives user acquisition and product-led growth strategies. He hosts the OWOX podcast, interviewing analytics professionals about data-driven marketing, attribution, and reporting best practices. Vadym specializes in turning complex analytics concepts into practical, actionable marketing frameworks.