LEFT, RIGHT, MID in Google Sheets - Deep Dive

Overview - LEFT, RIGHT, MID

What is it?

LEFT, RIGHT, and MID are functions in Google Sheets that help you extract parts of text from a cell. LEFT takes characters from the start of the text, RIGHT takes characters from the end, and MID takes characters from the middle based on a starting position and length. These functions let you break down text into smaller pieces easily.

Why it matters

These functions solve the problem of needing specific parts of text without manually copying or typing. Without them, you would waste time and make mistakes extracting data like first names, codes, or dates hidden inside text. They make data cleaning and analysis faster and more accurate.

Where it fits

Before learning these, you should know how to enter text and basic formulas in Google Sheets. After mastering these, you can learn more advanced text functions like SPLIT, FIND, and REGEXEXTRACT to handle complex text tasks.

Mental Model

Core Idea

LEFT, RIGHT, and MID let you cut out exactly the piece of text you want from the start, end, or middle of a string.

Think of it like...

Imagine a long ribbon with letters printed on it. LEFT cuts a piece from the left end, RIGHT cuts from the right end, and MID cuts a piece from anywhere in the middle.

Text:  H E L L O  W O R L D
       ↑           ↑       ↑
      LEFT       MID     RIGHT
       |           |       |
       |           |       |
       └─ Extract from start  └─ Extract from end
                   └─ Extract from middle

Build-Up - 7 Steps

1

Foundation

Extracting text from the start with LEFT

Concept: Learn how LEFT extracts a set number of characters from the beginning of text.

The LEFT function takes two inputs: the text and the number of characters to take from the start. For example, =LEFT("Google", 3) returns "Goo" because it takes the first 3 letters.

Result

The formula =LEFT("Google", 3) outputs "Goo".

Understanding LEFT helps you quickly grab the beginning part of any text, like first names or codes.

2

Foundation

Extracting text from the end with RIGHT

Concept: RIGHT extracts characters from the end of a text string.

RIGHT also takes two inputs: the text and how many characters to take from the end. For example, =RIGHT("Google", 3) returns "gle" because it takes the last 3 letters.

Result

The formula =RIGHT("Google", 3) outputs "gle".

RIGHT lets you grab endings like file extensions or last names easily.

3

Intermediate

Extracting text from the middle with MID

🤔Before reading on: do you think MID needs to know where to start and how many characters to take? Commit to your answer.

Concept: MID extracts characters from anywhere inside text by specifying a start position and length.

MID takes three inputs: the text, the starting position (counting from 1), and how many characters to take. For example, =MID("Google", 2, 3) returns "oog" because it starts at the 2nd letter and takes 3 letters.

Result

The formula =MID("Google", 2, 3) outputs "oog".

MID gives you precise control to cut out any part of text, not just the start or end.

4

Intermediate

Using numbers and cell references in LEFT, RIGHT, MID

🤔Before reading on: do you think you can use a number stored in another cell to decide how many characters to extract? Commit to yes or no.

Concept: You can use numbers or cell references for the length or start position in these functions.

Instead of typing a number directly, you can use a cell reference. For example, if A1 contains 4, =LEFT("Google", A1) returns "Goog". This makes formulas flexible and dynamic.

Result

If A1=4, then =LEFT("Google", A1) outputs "Goog".

Using cell references lets you change extraction lengths without editing formulas.

5

Intermediate

Handling text shorter than requested length

🤔Before reading on: if you ask LEFT for 10 characters but the text has only 6, what happens? Commit to your answer.

Concept: If the requested length is longer than the text, the function returns the whole text without error.

For example, =LEFT("Hi", 5) returns "Hi" because the text is shorter than 5 characters. Google Sheets does not give an error but simply returns what it can.

Result

The formula =LEFT("Hi", 5) outputs "Hi".

Knowing this prevents errors and helps you trust these functions with varying text lengths.

6

Advanced

Combining LEFT, RIGHT, MID for complex extraction

🤔Before reading on: do you think you can combine these functions to extract parts like first and last names from full names? Commit to yes or no.

Concept: You can nest or combine these functions to extract multiple parts from text strings.

For example, to get the first letter and last letter of "Google", use =LEFT("Google",1) & RIGHT("Google",1) which returns "Ge". You can also use MID inside LEFT or RIGHT for more complex patterns.

Result

The formula =LEFT("Google",1) & RIGHT("Google",1) outputs "Ge".

Combining these functions unlocks powerful text manipulation without extra tools.

7

Expert

Using LEFT, RIGHT, MID with dynamic positions via FIND

🤔Before reading on: can you use FIND inside MID to extract text based on where a character appears? Commit to your answer.

Concept: By using FIND to locate characters, you can dynamically extract text with MID, LEFT, or RIGHT based on content, not fixed positions.

For example, to get the first name from "John.Doe", use =LEFT(A1, FIND(".", A1) - 1). FIND finds the dot position, and LEFT extracts text before it. This adapts to different names automatically.

Result

If A1="John.Doe", the formula outputs "John".

Combining FIND with these functions creates flexible formulas that adapt to changing data.

Under the Hood

LEFT, RIGHT, and MID work by counting characters in a text string starting from either the left (start), right (end), or a specified position. Internally, Google Sheets treats text as a sequence of characters indexed from 1. LEFT counts forward from 1, RIGHT counts backward from the end, and MID counts forward from the given start. The functions then return the substring of requested length without altering the original text.

Why designed this way?

These functions were designed to be simple and intuitive for common text extraction needs. Counting characters from the start or end matches how people naturally think about text. MID adds flexibility for more complex cases. Alternatives like regular expressions are more powerful but harder for beginners. This design balances ease of use with practical power.

┌───────────────┐
│   Text String │
│ "Spreadsheet"│
└─────┬─────────┘
      │
      │
┌─────▼─────┐   ┌─────────────┐   ┌─────────────┐
│ LEFT(n)   │   │ MID(start,n)│   │ RIGHT(n)    │
│ Counts    │   │ Counts from │   │ Counts from │
│ from left │   │ start pos   │   │ right end   │
└─────┬─────┘   └─────┬───────┘   └─────┬───────┘
      │               │               │
      ▼               ▼               ▼
  Extracts first  n chars  Extracts n chars  Extracts last n chars
  from start              from middle       from end

Myth Busters - 4 Common Misconceptions

Quick: Does LEFT("Hello", 0) return an error or empty text? Commit to your answer.

Common Belief: LEFT with zero length will cause an error or return the whole text.

Reality: LEFT("Hello", 0) returns an empty string "" without error.

Why it matters: Expecting an error can cause confusion or unnecessary error handling in formulas.

Quick: Does MID("Hello", 6, 2) return an error or empty text? Commit to your answer.

Common Belief: MID starting beyond text length causes an error.

Reality: MID("Hello", 6, 2) returns an empty string "" without error.

Why it matters: Knowing this prevents formula breaks when data is shorter than expected.

Quick: Does RIGHT("Hello", -3) work or cause an error? Commit to your answer.

Common Belief: Using a negative number for length in RIGHT or LEFT works as a reverse count.

Reality: Negative numbers for length cause an error in these functions.

Why it matters: Using negative numbers mistakenly breaks formulas and wastes debugging time.

Quick: Can LEFT extract characters from the middle of text? Commit to yes or no.

Common Belief: LEFT can extract characters from anywhere in the text if you specify a start position.

Reality: LEFT always extracts from the start; it cannot start from the middle.

Why it matters: Misunderstanding this leads to wrong formulas and frustration when trying to extract middle text.

Expert Zone

1

LEFT, RIGHT, and MID count characters, not bytes, so they handle multi-byte characters like emojis correctly in Google Sheets.

2

When combining these functions with FIND, careful handling of off-by-one errors is crucial to avoid cutting extra or missing characters.

3

Using these functions inside ARRAYFORMULA can process multiple cells at once, but requires consistent input lengths to avoid unexpected blanks.

When NOT to use

Avoid using LEFT, RIGHT, MID when you need to split text by delimiters or patterns; instead, use SPLIT or REGEXEXTRACT for more flexible and powerful text parsing.

Production Patterns

Professionals often combine LEFT, RIGHT, MID with FIND or SEARCH to extract structured data like dates, codes, or names from messy text. They also use these functions inside larger formulas for dynamic dashboards and reports that adapt to changing data.

Connections

Regular Expressions (Regex)

More powerful text extraction method that builds on the idea of cutting text but uses patterns instead of fixed positions.

Understanding LEFT, RIGHT, MID helps grasp the basics of substring extraction before moving to pattern-based extraction with regex.

String slicing in programming languages

LEFT, RIGHT, MID are spreadsheet versions of string slicing found in languages like Python or JavaScript.

Knowing these functions prepares you to understand how programming languages handle text substrings.

Cutting fabric in tailoring

Both involve cutting a larger piece into smaller parts based on measurements and positions.

Recognizing this connection helps appreciate the precision and planning needed when extracting text or cutting materials.

Common Pitfalls

#1 Using LEFT with a negative number for length.

Wrong approach:

=LEFT("Hello", -2)

Correct approach:

=LEFT("Hello", 2)

Root cause:

Misunderstanding that length must be a positive number causes formula errors.

#2 Trying to extract middle text with LEFT instead of MID.

Wrong approach:

=LEFT("Spreadsheet", 5)

Correct approach:

=MID("Spreadsheet", 4, 5)

Root cause:

Confusing LEFT as a general substring extractor rather than only from the start.

#3 Hardcoding numbers instead of using cell references for length.

Wrong approach:

=RIGHT(A1, 3)

Correct approach:

=RIGHT(A1, B1)

Root cause:

Not using cell references reduces formula flexibility and requires manual edits.

Key Takeaways

LEFT, RIGHT, and MID are essential functions to extract parts of text from the start, end, or middle respectively.

They work by counting characters and returning substrings without changing the original text.

Using cell references for lengths and positions makes formulas dynamic and adaptable.

Combining these functions with FIND or SEARCH unlocks powerful, flexible text extraction.

Understanding their limits and common pitfalls prevents errors and improves spreadsheet reliability.