5 must-know text functions you should know in excel

5 must-know text functions

Excel text functions are very useful in cleaning the data, extracting a part of data, joining the data, or searching the position of a word. Few are easy to use but some need the practice to use correctly. In this article, let’s learn 5 must-know text functions to elevate our proficiency in excel.

The formula that we will be learning is LEFT , RIGHT , MID , LEN and FIND.

LEFT function used in extracting some part from the left side of a text string. The syntax for LEFT function is as follow.

5 must-know text functions in excel
5 must-know text functions in excel

Here text is the text string from where we wanted to extract text and [num_chars] is the number of text that we wanted to extract. Let us see how this works.

Left function
Left Function

In the above example, we have a text string in cell C4, now we wanted to extract 3 letters from the left. To extract this we will use the LEFT function. see the red highlighted rectangle where we used LEFT(C4,3). It will give us value She the first three.

RIGHT used in extracting some part from the right side of a text string. It is just the opposite of LEFT. The syntax for the RIGHT function is as follows.

Right function
Right Function

In the above image text is the text string from where we wanted to extract text and [num_chars] is the number of text that we wanted to extract. Let us see an example of this.

Right Function Extract
Right Function Extract

In the above example, we have a text string in cell C4, now we wanted to extract 3 letters from the right. To extract this we will use the RIGHT function, see the red highlighted rectangle where we used RIGHT(C4,3). It will give us value ore the last three.

MID helps us in extracting texts from the middle of a text string. The syntax for the MID function would be as follows.

MID fucntion
MID function

In the above image text is the text string from where we wanted to extract text, start_num is the number from where we wanted to start the extraction, and [num_chars] is the number of text that we wanted to extract. Let us see an example of this.

MID fucntion
MID function

In the above example, we have a text string in cell C4, now we wanted to extract 5 letters from the mid of the text string. To extract this we will use the MID function, see the red highlighted rectangle where we used MID(C4,5,5). It will give us value sells. One point to be noted here is that the last number of start_num will be the start point of extraction of text.

LEN used to find out the length of any text string. It is very useful in finding positions for any text when every text is of varying length. The syntax for LEN will very simple as illustrated below.

LEN function in excel
Syntax of Len function

Here text means the text string from for which we wanted to know the length. Let us see the below example.

Len Function
Len Function

It is self-explanatory how this function works. Just use the function and put the reference of a text string within the bracket.

FIND could be used to get the position number of any text in a text string. It is to be noted that it picks the position of the first occurrence of the text in the test string. Let us understand the syntax of the FIND function.

FIND function
FIND function

In the above syntax find_text is the text for which wanted to find the position, within_text is the text string in which we are trying to find the position, and [start_num] is the number from where we wanted to start the search. Let us see an example to understand this.

Find function
Find function

In the above example, we are trying to find the position of sea within the text string in C4. We will use the FIND function and put the parameters as mentioned in the first highlighted rectangle. It will give us a number 11 which is basically the position of sea from the initial of the text string.

Leave a Comment

Your email address will not be published. Required fields are marked *