LEFT(), RIGHT(), MID() in Advanced Excel

In Excel, text manipulation is a common task. The LEFT(), RIGHT(), and MID() functions are used to extract specific parts of a text string. These functions are especially useful when you need to isolate specific characters or segments from text data, such as extracting dates, product codes, or names from longer strings.

LEFT() Function

The LEFT() function is used to extract a specified number of characters from the beginning of a text string.

LEFT() Syntax

LEFT(text, num_chars)
  • text: The original text string from which to extract characters.
  • num_chars: The number of characters to extract, starting from the left of the text string.

Real-Time Example of LEFT()

Scenario: You have a list of product codes, and you want to extract the first 3 characters (representing the product category).

        Product Code
        ABC123
        DEF456
        GHI789
        JKL012

Steps:

  1. In cell B1, enter the following formula to extract the first 3 characters of the product code:
    2. =LEFT(A1, 3)
  2. Explanation: This formula extracts the first 3 characters of the text in cell A1. The result for "ABC123" will be "ABC", for "DEF456" it will be "DEF", and so on.

RIGHT() Function

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

RIGHT() Syntax

RIGHT(text, num_chars)
  • text: The original text string from which to extract characters.
  • num_chars: The number of characters to extract, starting from the right of the text string.

Real-Time Example of RIGHT()

Scenario: You have a list of phone numbers, and you want to extract the last 4 digits (the area code).

        Phone Number
        123-456-7890
        987-654-3210
        555-123-4567

Steps:

  1. In cell B1, enter the following formula to extract the last 4 digits of the phone number:
    2. =RIGHT(A1, 4)
  2. Explanation: This formula extracts the last 4 characters of the text in cell A1. The result for "123-456-7890" will be "7890", for "987-654-3210" it will be "3210", and so on.

MID() Function

The MID() function is used to extract a specific number of characters from the middle of a text string, starting at any position.

MID() Syntax

MID(text, start_num, num_chars)
  • text: The original text string from which to extract characters.
  • start_num: The position of the first character to extract (starting from 1).
  • num_chars: The number of characters to extract starting from the specified position.

Real-Time Example of MID()

Scenario: You have a list of email addresses, and you want to extract the domain name (the part after the "@").

        Email Address
        john.doe@gmail.com
        jane.smith@yahoo.com
        mike.jones@outlook.com

Steps:

  1. In cell B1, enter the following formula to extract the domain name from the email address:
    2. =MID(A1, FIND("@", A1) + 1, LEN(A1) - FIND("@", A1))
  2. Explanation: This formula uses the FIND() function to locate the "@" symbol in the email address. It then uses MID() to extract all characters after the "@" symbol. The result for "john.doe@gmail.com" will be "gmail.com", for "jane.smith@yahoo.com" it will be "yahoo.com", and so on.

Key Differences Between LEFT(), RIGHT(), and MID()

  • LEFT(): Extracts characters from the beginning of the text string.
  • RIGHT(): Extracts characters from the end of the text string.
  • MID(): Extracts characters from any position in the middle of the text string.

Combining LEFT(), RIGHT(), and MID()

Sometimes, you may need to combine these functions to manipulate more complex text data. For example, if you need to extract a part of a string that is not at the start or end, or if you need to isolate multiple parts of a string.

Real-Time Example of Combining LEFT(), RIGHT(), and MID()

Scenario: You have a list of dates in the format "YYYY-MM-DD" and want to extract the year, month, and day separately.

        Date
        2024-01-15
        2023-12-25
        2025-06-30

Steps:

  1. In cell B1, enter the following formula to extract the year:
    2. =LEFT(A1, 4)
  2. In cell C1, enter the following formula to extract the month:
    3. =MID(A1, 6, 2)
  3. In cell D1, enter the following formula to extract the day:
    4. =RIGHT(A1, 2)
  4. Explanation: The LEFT() function extracts the first 4 characters (the year), the MID() function extracts the characters starting from the 6th position (the month), and the RIGHT() function extracts the last 2 characters (the day).

Conclusion

The LEFT(), RIGHT(), and MID() functions are powerful tools in Excel for manipulating text data. They allow you to extract specific portions of a text string, whether it's from the beginning, the end, or the middle. By combining these functions, you can easily manipulate and analyze text in Excel, helping you organize and process your data more efficiently.




Advertisement