Explanation

LEFT, MID, RIGHT are Excel’s most basic Text Functions that enable us to extract a specific part from a text string.

For example, we can use Text Function to extract the first three letters of a word, the last 5 letters or 10 letters from the middle of the sentence.

LEFT is used to extract the leftmost letters of a text/cell, RIGHT does the same for the rightmost letters and MID works from the middle.

Each of these functions can extract a specific amount of letters that will be defined by the user when writing the function.

Let’s see how it works!

Syntax

Let’s start with the LEFT function:

=LEFT(text, num_chars)

text – This can be either a string that you type, or a cell reference from which we want to extract our substring.

num_chars – How many characters we would like to extract from the left?

Let’s say we want to extract the first 4 characters in the text “Vegemite Sandwich”:

 =LEFT(“Vegemite Sandwich”,4)

Our result will be: “Vege”

 

Now, let’s continue with the RIGHT function:

The syntax is pretty similar to the previous function:

=RIGHT(text, num_chars)

Only this time, we will be extracting the number of characters in num_chars from the right part of the text.

For example:

=RIGHT(“Hello World”,5)

Will give us the word “World”, which is comprised of the last 5 letters in the text.

 

The last (but not least) function is MID, which is capable of extracting a specific amount of letters from the middle of the text. Its syntax is:

=MID(text,start_num,num_chars)

text – The text from which we want to extract

start_num – What is the position of the first character we want to start extracting?

num_chars – From the start_num position, how many characters would we like to extract?

Let’s say we want to try to extract the word “quick” from the text “the quick brown fox jumps over the lazy dog”

For that, we will write the function:

=MID(“the quick brown fox jumps over the lazy dog”,5,5)

text – “the quick brown fox jumps over the lazy dog” – This is our original text

5 – start_num – This is the position of our starting number. We counted 5 characters, as “the” is 3 characters, and the space between the words is also counted as a character (3+1 = 4, meaning that next character will be the 5th)

5 – num_chars – How many characters should we extract, starting from the position we stated in start_num

 

Important note – For those of you who use Right to Left languages, such as Hebrew or Arabic, please note that LEFT, MID and RIGHT will work in the “opposite” direction, as they are based on the directions of the Left to Right languages such as English. This means that LEFT function will extract the letters from the right part of the text, while RIGHT will extract the letters from the left part of the text. So to avoid confusion, just think of LEFT as “Start” of the text, and RIGHT as the “End” of the text, regardless of the language you use 🙂

Practice Basic Text Functions – LEFT, MID, RIGHT

So, now it’s time to see how this functions work: