LEFT, RIGHT, MID in Google Sheets - Step-by-Step Guide

Introduction

These functions help you get parts of text from a cell. LEFT takes characters from the start, RIGHT takes from the end, and MID takes from the middle. They are useful when you want to split or extract specific pieces of text.

When you want to get the first few letters of a product code to identify its category.

When you need the last digits of a phone number for quick reference.

When you want to extract a middle part of a text like a date or ID from a longer string.

When you have a full name and want to get just the first name or last name.

When you want to separate parts of a code that are combined in one cell.

Steps

Step 1: Click

- cell where you want the result

The cell is selected and ready for input

Step 2: Type

- the formula bar

You start entering the formula

💡 Start with =LEFT, =RIGHT, or =MID depending on what part you want

Step 3: Enter

- formula bar

Formula appears in the cell showing the extracted text

Step 4: For LEFT, type

- formula bar

Formula looks like =LEFT(A1, 7) to get first 7 characters from cell A1

Step 5: For RIGHT, type

- formula bar

Formula looks like =RIGHT(A1, 3) to get last 3 characters from cell A1

Step 6: For MID, type

- formula bar

Formula looks like =MID(A1, 8, 4) to get 4 characters starting from 8th character in A1

Before vs After

Before

Cell A1 contains text 'Invoice2024XYZ'

After

Using =LEFT(A1, 7) shows 'Invoice', =RIGHT(A1, 3) shows 'XYZ', =MID(A1, 8, 4) shows '2024'

Settings Reference

Number of characters

📍 Second argument in LEFT and RIGHT functions

Defines how many characters to extract from start or end

Default: No default, must specify

Start position

📍 Second argument in MID function

Defines the position in text where extraction begins

Default: No default, must specify

Number of characters (MID)

📍 Third argument in MID function

Defines how many characters to extract starting from start position

Default: No default, must specify

Common Mistakes

Not specifying the number of characters in LEFT or RIGHT

The formula will return an error because the function needs to know how many characters to extract

Always include the number of characters as the second argument, like =LEFT(A1, 3)

Using MID with a start position outside the text length

The formula returns an empty string because it starts beyond the text

Check the text length and use a start position within that range

Using negative numbers for number of characters

Functions do not accept negative numbers and will give an error

Use only positive numbers for character counts

Summary

LEFT, RIGHT, and MID extract parts of text from cells.

LEFT gets characters from the start, RIGHT from the end, MID from the middle.

Always specify how many characters to extract and where to start for MID.

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