Excel has so many functions that either one of their or their combination can fulfill most of the tasks required in our work places. Out of them, one of the subcategory that is most commonly used is the TEXT functions. They are meant to manipulate texts within a formula.

The text functions are meant to manipulate and not format the text i.e. if you try to change the color of the text, that will not be possible, but if try to extract the left most or right most character of a text string, that is quite possible with these text formulas.

Besides, with text formulas, you can extract, join or concatenate, clean and remove characters, get character codes, perform searches within text strings, make substitution, trim the text, change case of text and convert text to numeric values and vise versa.

Out of these handful of functions will be taking up few to be explained in this post. For this post, we will be understanding how to use LEFT(), MID() and RIGHT() functions. The names are self explanatory, but following lines explain the syntax one by one:

An important concept – The Placeholder:

Just like we have seats to accommodate students in the class room and the hooks in the key holder for the keys, we have placeholders for characters in a string.  The place holder is similar to a seat where a character should sit in.

In order to understand how the following formula works, we need to understand this concept first. Consider a string “this_is_my_school”. Each character of this string is being sat in a placeholder like below:

We when we refer to the first character of the string, we will be taking about the place holder # 1 that contains “t”, and so forth. This concept will help us understanding the following discussion.

LEFT(text,[num_chars]):

The first argument is the text we want to get from something to the left. The number of character specifies how many characters we want to extract. If we omit the second argument which is optional, we will get the left most character.

Thus if we have string this_is_my_school, the formula =LEFT(“this_is_my_string”) will return “t”, similarly, the formula =LEFT(“this_is_my_string”,2) will return “th”.

RIGHT(text,[num_chars]):

Again, the first argument is the text we want to get from something from the extreme right. The number of character specifies how many characters we want to extract and if omitted the second argument, we will get the right most character.

Taking up the same example from previous lines, the formula =RIGHT(“this_is_my_string”) will return “g” and the formula =RIGHT(“this_is_my_string”,2) will give “ng”.

MID(text,start_num,num_chars)

This is the most vertisle and useful formula of the series, and can easily substitute the LEFT() and RIGHT() function if used properly. The formula takes three arguments, and none is optional i.e. we have to provide all the three arguments.

The first one is the text that we want to work on, in our case it is “this_is_my_string”. The second argument is the point or number of character from where we want to get the text. Note that for LEFT() function, this option was set to the extreme left i.e. the first character of the string, and we had to only specify the number of characters to be picked, and for RIGHT(), the extreme right one.

Thus MID() gives us the flexibility to start from any where in within the text. The last option asks us how many characters we want to pick, and is similar to the second option of the two preceding functions.

A twist in Text function:

What if you want to find the last to omit the only last two characters of a string? The LEFT() and the MID() formula will allow you to start from the left most side but will take you all the way long to the end, and with uneven lengths of strings, a fixed length formula would not work. For example formula three strings have different lengths.

The problem can be solved by adopting an adjust with function LEN() that give the length of the string, the solution works accurately provided the length of the part to be omitted is fixed. For our case the length is 3 i.e. the last three characters (the numeric part) is to be dropped. The following table shows the results.

With a simple change, we can intelligently manipulate and extract the information we need. We just need to know what formula to use and how to change it to adapt to the changes in our data.