LEFT, RIGHT, and MID Functions in Excel: Extracting Specific Characters Made Simple

Have you ever come across a situation in Excel where you only needed part of the text from a cell? Maybe you had a long product code and only needed the first few characters. Or maybe you had a list of names and wanted to extract just the last name. That’s where the LEFT, RIGHT, and MID functions in Excel come to the rescue.

These three text functions are like little “scissors” that help you cut out exactly the part of the text you need. The best part? They’re super easy to learn and can save you a lot of time when working with messy or complex data.

In this article, we’ll break down:

• What the LEFT, RIGHT, and MID functions do.

• How to write their formulas.

• Real-life examples of using each function.

• Tips and tricks to maximize their use in your everyday work.

So, let’s jump right in!

1. Understanding the Basics

LEFT Function

The LEFT function allows you to take a specific number of characters starting from the left side of a text string.

Formula:

=LEFT(text, num_chars)

• text: The cell or text you want to extract from.

• num_chars: The number of characters you want to pull out, starting from the left.

Example:
If cell A1 contains ExcelRocks, then:

=LEFT(A1, 5)

will return Excel.

RIGHT Function

The RIGHT function works the opposite way. It pulls out characters starting from the right side of a text string.

Formula:

=RIGHT(text, num_chars)

Example:
If A1 = ExcelRocks, then:

=RIGHT(A1, 5)

will return Rocks.

MID Function

The MID function is more flexible. It allows you to extract characters starting from any position in the text string, not just the left or right.

Formula:

=MID(text, start_num, num_chars)

• text: The cell or string to extract from.

• start_num: The position where extraction begins.

• num_chars: The number of characters you want to take.

Example:
If A1 = ExcelRocks, then:

=MID(A1, 6, 5)

will return Rocks (starting from the 6th character and taking 5 characters).

2. Practical Examples of LEFT, RIGHT, and MID

Now that you know the basics, let’s see how these functions are used in real life.

a. Extracting Area Codes from Phone Numbers

Suppose you have a phone number: +62-8123456789.
If you only want the country code +62:

=LEFT(A1, 3)

b. Getting the Last 3 Digits of an ID

If you have an employee ID like EMP12345, and you only want 345:

=RIGHT(A1, 3)

c. Extracting First Names and Last Names

If cell A1 contains John Smith:

• To get the first name (John), you can use:

  =LEFT(A1,4)
  

• To get the last name (Smith), you can use:

  =RIGHT(A1,5)
  

d. Pulling Out Middle Characters

If you want to grab just the middle part of a product code, like AB-123-CD, and you only need 123:

=MID(A1, 4, 3)

3. Combining with Other Functions

These functions get even more powerful when combined with others, such as LEN, FIND, and SEARCH.

Example: Extracting a domain from an email address
If A1 = john.doe@gmail.com, and you want gmail.com:

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

How it works:

• FIND("@", A1) finds the position of the @ symbol.

• LEN(A1) gives the total length of the text.

• Subtracting gives you the number of characters to extract from the right.

4. Why These Functions Are Super Useful

• Data cleaning: Perfect for trimming down imported data that comes with unnecessary prefixes or suffixes.

• Formatting: Helps when you need to separate codes, names, or numbers into readable chunks.

• Automation: Once set up, these formulas automatically adjust as your data changes.

5. Tips and Tricks

1. Use LEN for flexibility
   Instead of manually counting characters, use LEN to make your formulas dynamic.

2. Combine with TRIM
   If your data has unwanted spaces, always clean it up with TRIM first.

3. Think of them as scissors
   LEFT cuts from the front, RIGHT cuts from the end, and MID lets you cut anywhere in the middle.

The LEFT, RIGHT, and MID functions in Excel may seem simple, but they’re incredibly powerful tools once you get the hang of them. They save you from tedious manual editing and make your data more manageable in just a few clicks.

Next time you’re working with long product codes, phone numbers, or even full names, remember that these three little functions are your best friends. With practice, you’ll find yourself using them almost daily without even realizing it!