Excel functions LEFT, RIGHT, and MID allow you to pull specific parts of text, making them powerful tools for organizing and analyzing data. They are among the Top 10 Excel Functions You Must Know.
These functions are especially useful when working with structured codes, like phone numbers, zip codes, barcodes....
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 lists car manufacturers, the vehicle model, and the VIN. Use the LEFT, RIGHT, and MID functions to extract each part of the VIN.
LEFT Function to Extract the WMI Code
The WMI (World Manufacturer Identifier) represents the first 3 characters of the VIN and identifies 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.
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.
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.
Online Excercise
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.
