When you export raw data from a legacy database into Microsoft Excel, the information is often squashed together into a single, unreadable cell. For example, an employee ID code might be exported as EMP-4590-NY. If your human resources software only needs the middle four digits (4590), you cannot manually retype thousands of lines. Instead, you must use Excel’s powerful trio of text extraction functions—LEFT, RIGHT, and MID—to mathematically slice the text apart.
Extracting Text with the LEFT Function
The LEFT function starts at the absolute first character on the left side of the cell and extracts exactly how many characters you tell it to.
The syntax is: =LEFT(text, num_chars)
If cell A2 contains the string EMP-4590-NY, and you only want to extract the first three letters (“EMP”) to determine the employee classification, type this into cell B2:
=LEFT(A2, 3)Excel starts at the left edge, counts exactly 3 characters, and outputs EMP.
Extracting Text with the RIGHT Function
The RIGHT function operates identically to the LEFT function, but it starts counting backwards from the absolute end of the string.
The syntax is: =RIGHT(text, num_chars)
If you only need to extract the two-letter state code (“NY”) from the end of EMP-4590-NY, type this into cell C2:
=RIGHT(A2, 2)Excel starts at the far right edge, counts backwards 2 characters, and outputs NY.
Extracting Text with the MID Function
The MID function is the most complex and powerful of the three, because it allows you to start the extraction process anywhere inside the string, completely ignoring the edges.
The syntax requires three arguments: =MID(text, start_num, num_chars)
- text: The cell containing the raw string (e.g., A2).
- start_num: The exact character position where you want the extraction to begin.
- num_chars: How many characters you want to extract from that starting point.
If you want to extract only the four-digit employee number (4590) from the string EMP-4590-NY, you must do some quick counting. The “E” is position 1, the “M” is 2, the “P” is 3, the hyphen “-” is 4, and the number “4” is position 5.
Therefore, your starting number is 5, and you want to extract exactly 4 characters.
=MID(A2, 5, 4)Excel skips the first four characters, starts reading at position 5, extracts the next four characters, and perfectly outputs 4590.