When you import raw data into Microsoft Excel from a legacy CRM database, it is often dumped into a single, chaotic cell. For example, a cell might contain an employee’s ID number, their department code, and their full name mashed together into one long string: ID4592-SALES-JOHNSON. If you need to isolate just the department code (“SALES”) to run a financial pivot table, you cannot simply copy and paste thousands of rows manually. To surgically slice and extract specific characters from a text string, you must use the LEFT, RIGHT, and MID functions.
Extracting Text from the Beginning (LEFT)
The LEFT function instructs Excel to start at the absolute first character on the left side of a cell and grab a highly specific number of characters.
The syntax is: =LEFT(text, num_chars)
If cell A2 contains ID4592-SALES-JOHNSON, and you only want to extract the 6-character employee ID (ID4592), you would click into B2 and type:
=LEFT(A2, 6)Excel starts on the left, counts out exactly 6 characters, and outputs the clean ID number into your new column.
Extracting Text from the End (RIGHT)
The RIGHT function operates identically to the LEFT function, but it starts counting backwards from the absolute end of the text string.
If you only want to extract the employee’s last name (JOHNSON), which happens to be exactly 7 characters long, you would type:
=RIGHT(A2, 7)Excel starts at the very last letter (“N”), counts 7 characters backward to the left, and cleanly extracts the name.
Extracting Text from the Middle (MID)
The MID function is the most powerful of the three, allowing you to surgical extract a string of text buried deep inside a chaotic cell, completely ignoring the beginning and the end of the data.
The syntax requires three arguments: =MID(text, start_num, num_chars)
- text: The target cell (A2).
- start_num: The exact character position where Excel should begin extracting.
- num_chars: How many characters it should pull.
To extract the department code (SALES), we know the word starts at the 8th character position (including the hyphen). The word itself is 5 characters long.
=MID(A2, 8, 5)Excel will jump directly to the 8th character, grab exactly 5 letters, and output the clean word “SALES”, completely ignoring the ID number and the employee’s last name.