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.
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.
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.
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.
- Tutorial – Automatically extracting or merging cells
- LEN counts the number of characters in a string
- SEARCH helps to find a specific character in a string
- TRIM cleans your string of the unexpected blank
- TEXTBEFORE and TEXTAFTER
- Split your text on a delimiter – TEXTSPLIT Function
Phil
24/03/2019 @ 13:37
=RIGHT(B2,3)
Shouldn't 3 be 4 in this formula?
pdp
Frédéric LE GUEN
24/03/2019 @ 14:20
True ! Thanks