Lesson#235:LEFT, RIGHT, and MID Functions: Text Manipulation Demystified

In the world of Excel, when it comes to extracting and manipulating text within cells, the LEFT, RIGHT, and MID functions are your go-to tools. These powerful text manipulation functions allow you to extract specific portions of text from a larger string, providing you with precision and control over your data. In this expert-level guide, we’ll delve into the intricacies of these functions, providing you with in-depth insights and a wealth of practical examples – five for each function – to master the art of text manipulation in Excel.

Understanding the LEFT, RIGHT, and MID Functions

Before we dive into practical examples, let’s briefly understand the purpose and syntax of these functions:

• LEFT(text, num_chars): Extracts a specified number of characters from the beginning (left) of a text string.
• RIGHT(text, num_chars): Extracts a specified number of characters from the end (right) of a text string.
• MID(text, start_num, num_chars): Extracts a specified number of characters from the middle of a text string, starting at a specific position.

Now, let’s explore these functions in action with practical examples.

Examples of the LEFT Function

Example 1: Extract the First Name

Suppose you have a list of full names in column A (e.g., “John Doe”) and you want to extract the first names. You can use the LEFT function like this:

=LEFT(A1, FIND(" ", A1) - 1)

This formula finds the position of the first space and extracts the characters to the left of it, giving you “John.”

Example 2: Get the Area Code

If you have phone numbers in column B (e.g., “(123) 456-7890”) and you want to extract the area codes, you can use LEFT like this:

=LEFT(B1, 5)

This formula grabs the first five characters from the left, resulting in “(123)”.

Example 3: Extract File Extensions

Suppose you have a list of filenames in column C (e.g., “document.pdf”) and you want to extract the file extensions. You can use LEFT like this:

=LEFT(C1, LEN(C1) - FIND(".", C1))

This formula finds the position of the period and extracts the characters to the left of it, giving you a “document”.

Example 4: Get the First Word

If you have sentences in column D (e.g., “The quick brown fox”) and you want to extract the first word, you can use LEFT like this:

=LEFT(D1, FIND(" ", D1) - 1)

This formula finds the first space and extracts the characters to the left of it, resulting in “The”.

Example 5: Extract Leading Zeros

Suppose you have values in column E (e.g., “00123”) with leading zeros, and you want to remove them. You can use LEFT like this:

=LEFT(E1, LEN(E1) - LEN(SUBSTITUTE(E1, "0", "")))

This formula counts the number of leading zeros and removes them, leaving you with “123”.

Examples of the RIGHT Function

Example 1: Extract Last Name

Imagine you have a list of full names in column A (e.g., “John Doe”) and you want to extract the last names. You can use the RIGHT function like this:

=RIGHT(A1, LEN(A1) - FIND(" ", A1))

This formula finds the position of the first space and extracts the characters to the right of it, giving you “Doe”.

Example 2: Get the Last Four Digits

If you have credit card numbers in column B (e.g., “1234-5678-9012-3456”) and you want to extract the last four digits, you can use RIGHT like this:

=RIGHT(B1, 4)

This formula grabs the last four characters from the right, resulting in “3456”.

Example 3: Extract File Types

Suppose you have a list of filenames in column C (e.g., “document.xlsx”) and you want to extract the file types (extensions). You can use RIGHT like this:

=RIGHT(C1, LEN(C1) - FIND(".", C1))

This formula finds the position of the period and extracts the characters to the right of it, giving you “xlsx”.

Example 4: Get the Last Word

If you have sentences in column D (e.g., “The quick brown fox”) and you want to extract the last word, you can use RIGHT like this:

=RIGHT(D1, LEN(D1) - FIND("@", SUBSTITUTE(D1, " ", "@", LEN(D1)-LEN(SUBSTITUTE(D1, " ", "")))))

This formula finds the last space, substitutes it with “@” to locate the position, and extracts the characters to the right of it, resulting in “fox”.

Example 5: Extract Suffixes

Suppose you have a list of names in column E (e.g., “John Jr.”) and you want to extract the suffixes (e.g., “Jr.”). You can use RIGHT like this:

=RIGHT(E1, LEN(E1) - LEN(SUBSTITUTE(E1, " ", "")))

This formula removes spaces and extracts the characters to the right, giving you “Jr.”.

Examples of the MID Function

Example 1: Extract the Middle Name

Imagine you have a list of full names in column A (e.g., “John David Doe”) and you want to extract the middle names. You can use the MID function like this:

=MID(A1, FIND(" ", A1) + 1, FIND(" ", A1, FIND(" ", A1) + 1) - FIND(" ", A1) - 1)

This formula finds the positions of the first and second spaces and extracts the characters in between, giving you “David”.

Example 2: Get the Area Code

Suppose you have phone numbers in column B (e.g., “(123) 456-7890”) and you want to extract the area codes. You can use MID like this:

=MID(B1, 2, 3)

This formula starts at the second character and extracts the next three characters, resulting in “123”.

Example 3: Extract Middle Section

Imagine you have strings in column C (e.g., “ABCDEFG”) and you want to extract the middle section (e.g., “CDE”). You can use MID like this:

=MID(C1, 3, 3)

This formula starts at the third character and extracts the next three characters, giving you “CDE”.

Example 4: Get the Date

Suppose you have dates in column D (e.g., “01/15/2023”) and you want to extract the day portion (e.g., “15”). You can use MID like this:

=MID(D1, 4, 2)

This formula starts at the fourth character and extracts the next two characters, resulting in “15”.

Example 5: Extract Non-Contiguous Text

Suppose you have a series of characters in column E (e.g., “A_B_C_D_E”) and you want to extract the non-contiguous text (e.g., “A_C_E”). You can use MID like this:

=MID(E1, 1, 1) & MID(E1, 4, 1) & MID(E1, 7, 1)

This formula extracts characters at positions 1, 4, and 7, resulting in “A_C_E”.

Conclusion

The LEFT, RIGHT, and MID functions in Excel are invaluable tools for text manipulation and extraction. By understanding their syntax and exploring these practical examples, you can gain precise control over your data, whether you need to extract names, split phone numbers, or obtain specific portions of text from larger strings. These functions are essential additions to your Excel toolbox, empowering you to efficiently manage and work with text-based data.