LEFT, RIGHT, MID extraction in Excel - Step-by-Step Guide

Introduction

These functions help you pull out parts of text from a cell. LEFT gets characters from the start, RIGHT from the end, and MID from the middle. They are useful when you want to separate or analyze pieces of text like names, codes, or dates.

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

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

When you want to pull a specific part of a text string, like the middle three letters of a code.

When you have full names in one cell and want to get just the first name or last name.

When you want to separate date parts stored as text, like extracting the month from '20230415'.

Steps

Step 1: Click

- a blank cell where you want the extracted text

The cell is selected and ready for formula input

Step 2: Type

- the formula bar

Formula appears in the cell and formula bar

💡 Start with =LEFT(text, number_of_characters) to get characters from the start

Step 3: In the formula, replace 'text' with the cell reference containing your text, for example A2

- formula bar

Formula updates to =LEFT(A2, number_of_characters)

Step 4: Replace 'number_of_characters' with how many characters you want to extract, for example 3

- formula bar

Formula looks like =LEFT(A2, 3)

Step 5: Press Enter

- keyboard

Cell shows the first 3 characters from the text in A2

Step 6: Repeat steps 2 to 5 using =RIGHT(text, number_of_characters) to get characters from the end

- formula bar

Cell shows the last specified characters from the text

Step 7: Use =MID(text, start_position, number_of_characters) to get characters from the middle

- formula bar

Cell shows characters starting at the position you specify for the length you choose

Before vs After

Before

Cell A2 contains 'ExcelFun2024'

After

Using =LEFT(A2,5) shows 'Excel'; using =RIGHT(A2,4) shows '2024'; using =MID(A2,6,3) shows 'Fun'

Settings Reference

text

📍 formula argument

The text you want to extract characters from

Default: none

number_of_characters

📍 formula argument

How many characters to extract

Default: none

start_position

📍 MID formula argument

The position in the text where extraction begins

Default: none

Common Mistakes

Using a number_of_characters larger than the text length

Excel will just return the whole text, which might confuse you if you expect an error

Check the text length with LEN() before extracting or use a smaller number

For MID, starting position is zero or negative

MID requires start position to be 1 or more; zero or negative causes an error

Always use a start position of 1 or higher

Not using quotes around text strings in formulas

Excel treats unquoted text as cell references and will show errors if they don't exist

Put text strings inside double quotes, like =LEFT("Hello", 2)

Summary

LEFT, RIGHT, and MID extract parts of text from the start, end, or middle.

You specify the text, how many characters to extract, and for MID, where to start.

Make sure numbers are within the text length and text strings are quoted.

Practice

1. Which Excel function extracts the first 3 characters from the text in cell A1?

easy

A. =MID(A1, 3, 3)

B. =LEFT(A1, 3)

C. =RIGHT(A1, 3)

D. =MID(A1, 2, 3)

Solution

1. Step 1: Understand LEFT function usage

   The LEFT function extracts characters starting from the beginning of the text. Using 3 as the second argument means it takes the first 3 characters.

2. Step 2: Compare options

   =LEFT(A1, 3) uses =LEFT(A1, 3), which correctly extracts the first 3 characters. =RIGHT(A1, 3) extracts from the end, =MID(A1, 3, 3) starts at position 3, and =MID(A1, 2, 3) starts at position 2.

3. Final Answer:

   =LEFT(A1, 3) -> Option B

4. Quick Check:

   LEFT gets first characters = LEFT(A1, 3) [OK]

Hint: Use LEFT for start, RIGHT for end, MID for middle [OK]

Common Mistakes:

• Using RIGHT instead of LEFT for start characters
• Confusing MID start position with length
• Using MID with wrong start position

2. Which formula correctly extracts the last 4 characters from cell B2?

easy

A. =RIGHT(B2, 4)

B. =LEFT(B2, 4)

C. =MID(B2, LEN(B2)-4, 4)

D. =MID(B2, 4, 4)

Solution

1. Step 1: Understand RIGHT function usage

   The RIGHT function extracts characters from the end of the text. Using 4 as the second argument means it takes the last 4 characters.

2. Step 2: Evaluate options

   =RIGHT(B2, 4) uses =RIGHT(B2, 4), which directly extracts the last 4 characters. =LEFT(B2, 4) extracts from the start, =MID(B2, LEN(B2)-4, 4) has an off-by-one error in start position, and =MID(B2, 4, 4) extracts from position 4 but not from the end.

3. Final Answer:

   =RIGHT(B2, 4) -> Option A

4. Quick Check:

   RIGHT gets last characters = RIGHT(B2, 4) [OK]

Hint: RIGHT gets last characters; use length number [OK]

Common Mistakes:

• Using LEFT instead of RIGHT for last characters
• Incorrect MID start position calculation
• Confusing length with start position

3. If cell C3 contains the text "Spreadsheet", what is the result of =MID(C3, 4, 5)?

medium

A. "pread"

B. "sheet"

C. "dshee"

D. "eadsh"

Solution

1. Step 1: Identify the MID function parameters

   MID(text, start_num, num_chars) extracts characters starting at position 4 for 5 characters from "Spreadsheet".

2. Step 2: Extract characters from "Spreadsheet"

   Characters: S(1) p(2) r(3) e(4) a(5) d(6) s(7) h(8) e(9) e(10) t(11). Starting at 4: e, a, d, s, h -> "eadsh".

3. Final Answer:

   "eadsh" -> Option D

4. Quick Check:

   MID extracts from middle = "eadsh" [OK]

Hint: Count start position carefully for MID [OK]

Common Mistakes:

• Starting count at 0 instead of 1
• Mixing up start position and length
• Miscounting characters in the text

4. The formula =MID(D4, 0, 3) is used to extract characters from cell D4. What is the issue with this formula?

medium

A. Start position cannot be zero; it must be 1 or more.

B. Length cannot be less than 3.

C. MID cannot extract from cell references.

D. Formula should use LEFT instead of MID.

Solution

1. Step 1: Check MID function start position rules

   The start position in MID must be 1 or greater. Zero is invalid and causes an error.

2. Step 2: Analyze the given formula

   The formula uses start position 0, which is not allowed. Length 3 is valid, and MID works with cell references.

3. Final Answer:

   Start position cannot be zero; it must be 1 or more. -> Option A

4. Quick Check:

   MID start position ≥ 1 [OK]

Hint: MID start position must be ≥ 1 [OK]

Common Mistakes:

• Using zero or negative start position
• Confusing length with start position
• Thinking MID can't use cell references

5. You have a list of product codes in column A like "AB12345XYZ". You want to extract the middle 5 digits (positions 3 to 7). Which formula in cell B1 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 required substring

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

2. Step 2: Use MID function with correct parameters

   MID(text, start_num, num_chars) = MID(A1, 3, 5) extracts characters from position 3 for 5 characters.

3. Final Answer:

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

4. Quick Check:

   MID extracts middle substring = MID(A1, 3, 5) [OK]

Hint: MID(start, length) extracts middle substring easily [OK]

Common Mistakes:

• Using LEFT or RIGHT instead of MID for middle parts
• Swapping start position and length
• Incorrect start position or length values