Bird
Raised Fist0
Excelspreadsheet~8 mins

LEFT, RIGHT, MID extraction in Excel - Dashboard Guide

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Dashboard Mode - LEFT, RIGHT, MID extraction
Dashboard Goal

Extract parts of product codes to analyze product categories, regions, and item numbers separately.

Sample Data
Product CodeFull Code Description
US12345AUnited States, Item 12345, Type A
EU98765BEurope, Item 98765, Type B
AS54321CAsia, Item 54321, Type C
AF67890DAfrica, Item 67890, Type D
SA11223ESouth America, Item 11223, Type E
Dashboard Components
  • KPI Card 1: Region Code
    Formula: =LEFT(A2,2)
    Extracts the first 2 letters representing the region from the product code.
  • KPI Card 2: Item Number
    Formula: =MID(A2,3,5)
    Extracts 5 characters starting from the 3rd character, representing the item number.
  • KPI Card 3: Product Type
    Formula: =RIGHT(A2,1)
    Extracts the last character representing the product type.
  • Summary Table
    Shows extracted parts side by side for easy comparison.
Dashboard Layout
+----------------+----------------+----------------+------------------+
| Region Code    | Item Number    | Product Type   | Summary Table    |
| (LEFT formula) | (MID formula)  | (RIGHT formula)| (All extracted)  |
+----------------+----------------+----------------+------------------+
Interactivity

User can filter product codes by region code using a dropdown filter. When a region is selected, all KPI cards and the summary table update to show only product codes from that region.

Self Check

If you add a filter to show only product codes starting with 'EU', which components update and what values do they show?

  • Region Code KPI: Shows 'EU' only.
  • Item Number KPI: Shows item numbers like '98765'.
  • Product Type KPI: Shows product types like 'B'.
  • Summary Table: Shows only rows with product codes starting with 'EU'.
Key Result
Dashboard extracts and displays region, item number, and product type from product codes using LEFT, MID, and RIGHT formulas.

Practice

(1/5)
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
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard