Excel Text Extraction Functions: LEFT, RIGHT & MID Functions

Excel is a powerhouse for data manipulation, and mastering text functions can make your work faster and more efficient. Today, let's explore three essential text functions: LEFT, RIGHT, and MID.

1. LEFT Function – Extracting Characters from the Beginning

The LEFT function helps extract a specific number of characters from the start (left side) of a text string.

Syntax:

=LEFT(text, num_chars)        

? Example 1: If A2 contains "02-West-2635", then: =LEFT(A2, 2) → Output: "02"

2. RIGHT Function – Extracting Characters from the End

The RIGHT function is used to extract a certain number of characters from the end (right side) of a text string.

Syntax:

=RIGHT(text, num_chars)        

? Example 1: If A2 contains "02-West-2635", then: =RIGHT(A2, 4) → Output: "2635"

3. MID Function – Extracting Characters from the Middle

The MID function helps pull characters from anywhere within a text string, based on a starting position and the number of characters to extract.

Syntax:

=MID(text, start_num, num_chars)        

? Example 1: If A2 contains "02-West-2635", then: =MID(A2, 4, 4) → Output: "West"

After writing the function, select the cell and drag the fill handle down. You will notice that the MID function correctly extracts the expected results down to row 6. However, from row 7 onwards, the word "North" is missing its "h" because the MID function cannot dynamically adjust the number of characters extracted due to the fixed third argument (4). If the text length is more or less than 4 characters, it leads to incorrect data extraction. To avoid this issue, we need to act smart and nest the FIND function within MID, which we will discuss in the next section.

Best Practice: Using FIND with MID Function

When extracting data from text, we may not always know the exact position of the required text. This is where the FIND function becomes useful in combination with MID.

Example: Extracting the Middle Part Dynamically

If A7 contains "03-North-2318", and we need to extract "North" dynamically:

? Formula:

=MID(A7, 4, FIND("-", A7, 4) - 4)        

?? Explanation:

• FIND("-", A7, 4) locates the position of the second hyphen "-".
• -4 ensures the function extracts the correct number of characters dynamically.

Real-World Use Cases:

? Extracting first names from full names.

? Getting domain names from email addresses.

? Pulling specific data from structured text.

? Extracting order numbers or product IDs from codes.

? Dynamically extracting text when positions vary using FIND.

These functions are essential for cleaning and organizing text data efficiently. Try them out and let me know how they help in your Excel journey! ??

Do you want more such Excel tips? Drop a comment or hit the follow button for weekly insights! ??