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

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.

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.

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."

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)

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:

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!