How to use the LEFT, RIGHT, MID, and LEN functions in Excel

Writer
Updated on: March 19, 2023

When you want to split or concatenate cells in Microsoft Excel, you have a lot of aids that can help you do that. However, a couple of them can help you be more specific. Do you want to know which are them? Then keep reading to learn how to use Excel's LEFT, RIGHT, MID, and LEN functions because this is about to start!

  • The LEFT function
  • The Right Function
  • The MID function
  • The LEN function

The LEFT function

You can use the LEFT and RIGHT functions in Microsoft Excel to extract a specific number of characters from a text string's left or right side. These functions are advantageous when you have a large amount of data in a column and only need a specific part for your analysis.

On the one hand, the LEFT function allows you to extract a certain number of characters from the left side of a text string. Its syntax is quite simple:

=LEFT(text, num_chars)

  • The "text" argument is the text string from which you want to extract the characters. 
  • The "num_chars" argument is the number of characters you want to extract from the left side of the text string. 

For example, if you have the text string "Excel is a powerful tool" in cell A1. You want to extract the first 5 characters from the left side of the string, you would use the following formula:

=LEFT(A1, 5)

This formula would return "Excel" because it extracts the first 5 characters from the left side of the text string.

LSXHlWC7P2rQCDVUynL EzfJ9G3FGWOoqkvk Qr1veAzmDG4gnYdeYM1oNrwFE4ZvfJEklU NAFZa0VOGiBbTagzWPmP g4tV iXn5i1wJunUYWrE0kV0pbMNJzgHcZGn9HgTcXUEgXggqGTyFhoroE

The Right Function

The RIGHT function, on the other hand, allows you to extract a certain number of characters from the right side of a text string. As you can see, the following syntax is similar to the LEFT one:

=RIGHT(text, num_chars).

And the criterion between parentheses means the same as the last operations. Nevertheless, in this case, the "num_chars" argument corresponds to the characters’ number of the text you want to extract from the right to left. 

Returning to the previous example, if you want to extract the last 4 characters from the right side of the text "Excel is a powerful tool" in cell A1, you must use the following formula:

=RIGHT(A1;4).

This formula will return "tool" because it extracts the last 4 characters from the right side of the text string.

MMWjxOcEP1nDIKaK8YqcEcfUx6CcdV

It is important to note if the "num_chars" argument is greater than the length of the text string, the function will return the entire text string.

The MID function 

You can use this function in Excel to extract a specific number of characters from a text string, starting at a specified position. So, based on that, the syntaxis changes a little:

=MID(text, start_num, num_chars)

  • “Text”  is the argument where is the string of information you want to extract from.
  • “Start_num” is the position of the first character since you want to extract the information.
  • “Num_chars,” tells the number of characters you want to extract.

With the same example, if you want to avoid the words “Excel” and “Tool,” you must type:

=MID(A1;6;14) into a cell so that the result will be "is a powerful."

6JC3tPi8v21MaC K 0ROU3MGtvhEKqrR3y0ttR6F0wPSF8CDWvVIb5nGMpqNS7XSbSAxcW62Py0dt9RbNHTqEA4jXZ4jF20OUw6fbkCmZfQoZWk 00EH1wJRJesWi5m nhjyhm1QlSweaviFSrVBrkU

Note: The spaces also count as characters within the information set.

The LEN function

The LEN function returns the length of a text string in a cell following the syntax:

=LEN(text)

The "text" argument is the string you want to find the length. For example, if you have the text string "Excel is a powerful tool" in cell A1, you would use the following formula to find the length of the string:

=LEN(A1)

WIHQn8VG8EcVE7 Qw7goISY4g wi Nmeco rNq 4Ay o9uE3WV4 IZtQ4aR9Z8ljmYhVgC1

Notice that this formula would return "24" because the text string contains 24 characters.

You can use the LEN operation with the LEFT, MID or RIGHT functions to extract a variable number of characters from a text string. You only need to type the equal sign followed by the LEN function, and within the parenthesis, enter the function you need. Check this example:

zdTCGcfHtqNnPL0lVyYmNOwn1I2a

Notice that, in this example, the program is counting the first 5 characters corresponding to the “Excel” word.

In the end, all these functions in Microsoft Excel are great tools that allow you to extract a specific number of characters from a text string. These functions are convenient when you need to analyze a large amount of data and only need a particular part for your analysis. In addition, you can combine these functions with other Excel functions to create more complex operations.

Do you want to know more?

Every time you learn a new function in Excel, you become a master of this outstanding program. So if you want to know more and improve your productivity skills, try our Excel course at Skillademia! Then you can also check other fantastic courses and get your certification!

Lorena M. Rodas leverages her experience across film, writing, and production to make complex tech concepts accessible through storytelling. With a background spanning sci-fi, AI, and emerging tech, Lorena translates her depth of knowledge into engaging, educational content. She is an expert at decoding high-level topics to reach broad audiences.
Access 2,701+ video lessons. Instantly.
Start Learning Now →
crossmenu linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram