The LEFT and RIGHT functions are used to extract from the beginning and end of a text respectively.
Syntaxes=LEFT(string, [number_of_characters])
=RIGHT(string, [number_of_characters])string – The input string from whose left part is returned. number_of_characters [ Optional ] – This specifies the number of characters to return from the left/right side of the string. This argument takes a value of 1 by default.
Sample Usages=LEFT("119_MIS_Leonard", 3) or =LEFT(A2, 3) //Extracts the first three characters, 119, from the text.=RIGHT("https://www.bbc.com", 7)//Returns only the main part of the URL–bbc.com
Objective
Learn how to use the LEFT and RIGHT Functions in Google Sheets.
Introduction
LEFT and RIGHT functions in Google Sheets prove to be useful in situations where you only want to extract some useful information from the beginning (left) or end (right) of a text respectively. In simpler terms, these functions help you in extracting a substring from the beginning or end of a specified string of text.
For example, if you have a set of words such as “Leonard_122”, “Joey_135”, “Seinfeld_853” and you would like to just see the names (“Leonard”, “Joey”, “Seinfeld”) then in such a case, the Left function in Google Sheets would help you keep a defined number of initial characters.
Likewise, to change a format like “122_Leonard”, “135_Joey”, “853_Seinfeld to “Leonard”, “Joey”, “Seinfeld”, the Right function in Google Sheets would help you keep a defined number of characters at the end.
Sometimes, you may even want to eliminate characters both at the beginning as well as the end of a string – in such a case you combine Left and Right functions in Google Sheets as well.
LEFT Function Example
The spreadsheet shown below gives a list of students studying in Modern International School (MIS). As shown in the ID column, the ID of each student consists of three parts- roll number, name of school and name of the student. Let us consider a situation where we want to extract the roll number of each student from their given IDs.
We can easily do this by using the following LEFT function formula in Google Sheets.
=LEFT(B2,3)
Here B2 refers to the cell which contains the string from which we want to extract the left portion and the number 3 specifies that the first 3 characters from the left end of the string should be displayed as the output.
RIGHT Function Example
The following spreadsheet gives a list of URLs. As you can see these URLs start with an https:// or an https://www. Our goal here is to remove this portion from the URL and extract only the useful part of the URL. This is where the RIGHT Function comes in handy.
I have used the following formula to display the result.
=RIGHT(A2, LEN(A2)-8)
- A2 refers to the cell which contains the input string.
- LEN(A2) calculates the length of the string in cell A2.
- LEN(A2) – 8 specifies that the 8 characters of https:// should be removed from the string while displaying the output.
Since in cell A4, https://www. has 12 characters, I have modified the formula to the following in this case.
=RIGHT(A4, LEN(A4)-12)
As you can see in the figure above, the RIGHT Function extracts only the useful part from the right end of the URL and displays the same as output.
Conclusion
We learnt how to use Left and Right Functions in Google Sheets – they can often come in handy when you want to remove unnecessary characters in your data and make it easier to read the underlying text.
See Also
- Regular Expression Rules and Syntax: Learn about the different rules that can be used while making a REGEX expression.
- Regular Expression Formulas: Know more about the RegEx Formulas in Google Sheets.