Why LEFT, RIGHT, MID in Google Sheets? - Purpose & Use Cases

The Big Idea

Discover how a simple formula can save you hours of tedious copying and pasting!

The Scenario

Imagine you have a list of full names or product codes in a spreadsheet, and you need to extract just the first name, last few characters, or a middle section from each entry manually.

Doing this by hand means opening each cell, copying parts of the text, and pasting them elsewhere.

The Problem

This manual method is slow and boring, especially if you have hundreds of entries.

It's easy to make mistakes like copying the wrong part or missing some cells.

Also, if the data changes, you have to repeat the whole process again.

The Solution

The LEFT, RIGHT, and MID functions let you automatically pull out parts of text from any cell.

With just one formula, you can get the first few letters, last few letters, or any middle section you want.

This saves time, reduces errors, and updates instantly if your data changes.

Before vs After

✗ Before

Copy first 5 letters from each cell manually

✓ After

=LEFT(A1, 5)

What It Enables

You can quickly and accurately extract specific parts of text from many cells, making data cleaning and analysis much easier.

Real Life Example

Suppose you have a list of email addresses and want to extract just the username before the '@' sign to create personalized greetings.

Key Takeaways

Manual text extraction is slow and error-prone.

LEFT, RIGHT, MID automate pulling parts of text easily.

Formulas update automatically when data changes.

Practice

1. Which function would you use in Google Sheets to get the first 3 letters from the word "Banana"?

easy

A. RIGHT("Banana", 3)

B. LEFT("Banana", 3)

C. MID("Banana", 2, 3)

D. MID("Banana", 3, 3)

Solution

1. Step 1: Understand the function purpose

   The LEFT function extracts characters from the start of the text.

2. Step 2: Apply the function to the word

   LEFT("Banana", 3) returns the first 3 letters: "Ban".

3. Final Answer:

   LEFT("Banana", 3) -> Option B

4. Quick Check:

   LEFT = first letters [OK]

Hint: LEFT gets letters from start, RIGHT from end [OK]

Common Mistakes:

• Confusing LEFT with RIGHT function
• Using MID without correct start position
• Assuming MID always starts at 1

2. Which of these formulas correctly extracts the last 4 characters from the text "Spreadsheet"?

easy

A. RIGHT("Spreadsheet", 4)

B. LEFT("Spreadsheet", 4)

C. MID("Spreadsheet", 4, 4)

D. RIGHT("Spreadsheet", 5)

Solution

1. Step 1: Identify the function for last characters

   The RIGHT function extracts characters from the end of the text.

2. Step 2: Apply the function with correct number

   RIGHT("Spreadsheet", 4) returns the last 4 letters: "eet".

3. Final Answer:

   RIGHT("Spreadsheet", 4) -> Option A

4. Quick Check:

   RIGHT = last letters [OK]

Hint: RIGHT extracts from end; count how many letters you want [OK]

Common Mistakes:

• Using LEFT instead of RIGHT for last letters
• Choosing wrong number of characters
• Confusing MID start position with length

3. What is the result of the formula =MID("Adventure", 3, 4) in Google Sheets?

medium

A. "Adve"

B. "entu"

C. "dven"

D. "vent"

Solution

1. Step 1: Understand MID parameters

   MID(text, start, length) extracts length characters starting at position start.

2. Step 2: Apply to "Adventure"

   Starting at position 3: A(1), d(2), v(3), e(4), n(5), t(6), u(7), r(8), e(9). Extract 4 letters: v, e, n, t.

3. Final Answer:

   "vent" -> Option D

4. Quick Check:

   MID("Adventure",3,4) = "vent" [OK]

Hint: Count start position carefully, then take length letters [OK]

Common Mistakes:

• Starting count at 0 instead of 1
• Mixing up start position and length
• Assuming MID extracts from start or end

4. The formula =MID("Calculator", 0, 3) is used in a cell. What is the correct fix to get the first 3 letters?

medium

A. =MID("Calculator", 1, 3)

B. =LEFT("Calculator", 4)

C. =RIGHT("Calculator", 3)

D. =MID("Calculator", 3, 3)

Solution

1. Step 1: Identify the error in MID start position

   MID start position must be 1 or more; 0 is invalid and causes error.

2. Step 2: Correct the start position to 1

   Changing to MID("Calculator", 1, 3) extracts first 3 letters: "Cal".

3. Final Answer:

   =MID("Calculator", 1, 3) -> Option A

4. Quick Check:

   MID start must be ≥1 [OK]

Hint: MID start position must be 1 or more, never zero [OK]

Common Mistakes:

• Using 0 as start position in MID
• Using RIGHT or LEFT when MID is intended
• Changing length instead of start position

5. You have a list of product codes like "AB12345XYZ". You want to extract the middle 5 digits (positions 3 to 7). Which formula correctly extracts these digits?

hard

A. =RIGHT(A1, 5)

B. =LEFT(A1, 5)

C. =MID(A1, 3, 5)

D. =MID(A1, 5, 3)

Solution

1. Step 1: Identify the start position and length

   The digits start at position 3 and span 5 characters (positions 3,4,5,6,7).

2. Step 2: Use MID with correct parameters

   =MID(A1, 3, 5) extracts 5 characters starting at position 3, which are the digits.

3. Final Answer:

   =MID(A1, 3, 5) -> Option C

4. Quick Check:

   MID extracts middle characters by start and length [OK]

Hint: MID(text, start, length) extracts middle part exactly [OK]

Common Mistakes:

• Using LEFT or RIGHT instead of MID for middle extraction
• Wrong start position or length values
• Assuming digits are always at start or end