Functions LEFT – RIGHT – MID

Last Updated on 31/10/2024
Reading time: 2 minutes

Excel functions LEFT, RIGHT, and MID allow you to pull specific parts of text, making them powerful tools for organizing and analyzing data. These functions are especially useful when working with structured codes, like phone number, zipcode, barcode ....

Study Case: Let's split a VIN

A VIN (Vehicle Identification Number) is a unique 17-character code that identifies every vehicle. It’s divided into three main sections:

  • WMI (World Manufacturer Identifier): The first three characters reveal the manufacturer and country.
  • VDS (Vehicle Descriptor Section): Characters 4 to 9 describe the vehicle's model, engine type, and body style.
  • VIS (Vehicle Identifier Section): The last eight characters include the model year, plant, and unique serial number.

The following document contains a list of Car manufacturers, the vehicle's model, and the VIN. Let's use the functions LEFT, RIGHT, and MID to extract each part of the VIN.

VIN number examples

LEFT Function to Extract the WMI Code

The WMI (World Manufacturer Identifier) represents the first 3 characters of the VIN, identifying the vehicle’s country and manufacturer. To extract these initial characters in Excel, use the LEFT function. Here’s how:

  • Formula: =LEFT(C2, 3)
  • Explanation: This formula pulls the first 3 characters, where 3 specifies the number of characters to extract.
LEFT function to extract the x left characters of a string

RIGHT Function to Extract the VIS Code

The VIS (Vehicle Identifier Section) is the last 8 characters of the VIN. To capture this part, use the RIGHT function:

  • Formula: =RIGHT(C2, 8)
  • Explanation: This formula retrieves the final 8 characters, where 8 defines the length of the substring.
RIGHT function to extract the x right characters of a string

MID Function to Extract the VDS Code

The VDS (Vehicle Descriptor Section) occupies 6 characters, starting from the 4th position in a VIN. To extract this middle section, use the MID function:

  • Formula: =MID(C2, 4, 6)
  • Explanation: Here, 4 is the starting position, and 6 is the length of the substring.
MID function to extract the characters inside a string

Other TEXT functions in Excel

LEFT, RIGHT, and MID are crucial in extracting part of a string. Additionally, you can employ other functions to extract even more intricate substrings.

2 Comments

  1. Phil
    24/03/2019 @ 13:37

    =RIGHT(B2,3)

    Shouldn't 3 be 4 in this formula?

    pdp

    Reply

    • FrĂ©dĂ©ric LE GUEN
      24/03/2019 @ 14:20

      True ! Thanks

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Functions LEFT – RIGHT – MID

Reading time: 2 minutes
Last Updated on 31/10/2024

Excel functions LEFT, RIGHT, and MID allow you to pull specific parts of text, making them powerful tools for organizing and analyzing data. These functions are especially useful when working with structured codes, like phone number, zipcode, barcode ....

Study Case: Let's split a VIN

A VIN (Vehicle Identification Number) is a unique 17-character code that identifies every vehicle. It’s divided into three main sections:

  • WMI (World Manufacturer Identifier): The first three characters reveal the manufacturer and country.
  • VDS (Vehicle Descriptor Section): Characters 4 to 9 describe the vehicle's model, engine type, and body style.
  • VIS (Vehicle Identifier Section): The last eight characters include the model year, plant, and unique serial number.

The following document contains a list of Car manufacturers, the vehicle's model, and the VIN. Let's use the functions LEFT, RIGHT, and MID to extract each part of the VIN.

VIN number examples

LEFT Function to Extract the WMI Code

The WMI (World Manufacturer Identifier) represents the first 3 characters of the VIN, identifying the vehicle’s country and manufacturer. To extract these initial characters in Excel, use the LEFT function. Here’s how:

  • Formula: =LEFT(C2, 3)
  • Explanation: This formula pulls the first 3 characters, where 3 specifies the number of characters to extract.
LEFT function to extract the x left characters of a string

RIGHT Function to Extract the VIS Code

The VIS (Vehicle Identifier Section) is the last 8 characters of the VIN. To capture this part, use the RIGHT function:

  • Formula: =RIGHT(C2, 8)
  • Explanation: This formula retrieves the final 8 characters, where 8 defines the length of the substring.
RIGHT function to extract the x right characters of a string

MID Function to Extract the VDS Code

The VDS (Vehicle Descriptor Section) occupies 6 characters, starting from the 4th position in a VIN. To extract this middle section, use the MID function:

  • Formula: =MID(C2, 4, 6)
  • Explanation: Here, 4 is the starting position, and 6 is the length of the substring.
MID function to extract the characters inside a string

Other TEXT functions in Excel

LEFT, RIGHT, and MID are crucial in extracting part of a string. Additionally, you can employ other functions to extract even more intricate substrings.

2 Comments

  1. Phil
    24/03/2019 @ 13:37

    =RIGHT(B2,3)

    Shouldn't 3 be 4 in this formula?

    pdp

    Reply

    • FrĂ©dĂ©ric LE GUEN
      24/03/2019 @ 14:20

      True ! Thanks

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *