Whether you need to manipulate text data to standardize or clean it or extract specific characters from a cell, you can use the LEFT, RIGHT, and MID functions to do just that. These functions extract specific characters from anywhere in a cell—whether text or numbers.

So, if you want to separate the data in a cell into different columns, these are the functions you need. Here's how to use LEFT, RIGHT, and MID efficiently, saving you time and energy.

How to Use the LEFT Function to Extract Text From the Left Side of a String

The LEFT function allows you to extract a specified number of characters from the left side of a string. The syntax of this function is as follows:

LEFT(text, num_chars)

Here, text is the targeted string, and num_chars is the number of characters you want to extract.

Let's take a look at an example.

Say you have a spreadsheet containing your employees' email addresses, and you want to extract their usernames. You can use the LEFT function to do so. Here's how:

=LEFT(A2, FIND("@", A2) - 1)

LEFT function in Excel

The FIND function will find the position of the first space character in the text string. -1 will subtract the @ symbol and extract only the characters before it.

Similarly, suppose you have a list of shipped item codes, and each code consists of two alphabets followed by three numbers. You can use the LEFT function to separate the alphabetical code from the numbers.

Assuming the codes are in column A, and you want the new codes in column B, here is the LEFT function you will use:

=LEFT(A2, 2)

LEFT function in Excel

You can also further sort this data alphabetically using the relevant function or via VBA.

How to Use the RIGHT Function to Extract Text From the Right Side of a String

The RIGHT function allows you to extract a specified number of characters from the right side of a string.

Taking the second example we discussed above again, suppose you wish to separate the three-digit numerical code (on the right side of the string) from the shipped item codes in the spreadsheet.

Here's how to use the RIGHT function to do that:

=RIGHT(A2, 3)

RIGHT function in Excel

Let's take a look at another example.

Let's say you have a list of phone numbers in the (XXX) XXX-XXXX format. You want to extract the last four digits from the list. Assuming the phone numbers are in column A, you will use the RIGHT function as follows:

=RIGHT(A2, 4)

RIGHT function in Excel

A2 is the source cell, and 4 is the number of characters it will extract from the rightmost character in A2.

How to Use the MID Function to Extract Text From the Middle of a String

The MID function allows you to extract a specified number of characters from the middle side of a string. Here's the syntax of the MID function:

=MID(text, start, num_chars)

This is what each argument stands for in this function:

  • text is the targeted string from which you want to extract the characters.
  • start is the position from where you want to begin extracting the characters.
  • num_chars is the number of characters you wish to extract.

Let's have a look at an example:

Let's say you have a list of phone numbers in the (XXX)XXX-XXXX format, and you want to extract the three numbers in the middle from the list. Assuming the phone numbers are in column A, you will use the MID function as follows:

=MID(A2,6,3)

MID function in Excel

This formula will extract three numbers in the middle, starting from the 6th position in the numerical string. You can also arrange the data in ascending or descending order using the Sort function in Excel.

Here is another example.

Let's say you have a list of email addresses in a spreadsheet in the username@domain.com format, and you want to extract the domain name. Assuming the email addresses are in the A column, here is how you will use the MID function:

=MID(A2, FIND("@", A2)+1, LEN(A2))

MID function in Excel

Here, the FIND function will locate the position of @, and the LEN function will calculate the length of a text string to use in the num_chars argument.

Extract Data in Excel Quickly and Easily

When working with data in Excel, you should keep it consistent and standardized. If you're working with varied information, you can use the LEFT, RIGHT, and MID functions to achieve your goals. Using these, you can quickly and easily manipulate data to meet your specific requirements, making your data easier to work with.

Whether you're a beginner or an experienced user, mastering these functions will help you gain confidence and overcome fears about working with spreadsheets.