Do you want to extract some text from a cell, in Excel or in Google Sheets? Have you ever heard of the LEFT, RIGHT and MID functions?
The LEFT, RIGHT and MID functions are handy when you want to extract text from a specific cell. We’ll explain in this tutorial how to easily use these functions in Excel or in Google Sheets. Watch our video for more examples!
What is the LEFT function in Excel?
The LEFT function lets you extract characters from a text in a specific cell.
Syntax of the LEFT function: =LEFT(text,[num_chars]).
The function syntax has 2 arguments : text and [num_chars]; the last argument is optional.
* text – the cell containing the characters you want to extract.
* num_chars – how many characters you want to extract, starting from the left.
Notes
– If you don’t enter any value for [num_chars], the system assumes it means 0 if you entered =LEFT(text,). The cell containing the result will be empty.
– If you don’t enter any value for [num_chars], the system assumes it means 1 if you entered =LEFT(text).
Example
In this example, we want to extract the part of the references containing numbers. We notice the characters we want to extract are contained within the first 6 characters. We use 6 as the [num_chars] argument.
What’s the formula for the LEFT function in Google Sheets?
With this in mind, if you want to use the LEFT function in Google Sheets, you can use a similar syntax than the one in Excel: =LEFT(string, [number_of_characters]).
What is the RIGHT function in Excel?
The RIGHT function lets you extract characters from a text in a specific cell, starting from the right of the text string.
Syntax of the RIGHT function: =RIGHT(text,[num_chars]).
The function syntax has 2 arguments : text and [num_chars]; the last argument is optional.
* text – the cell containing the characters you want to extract.
* num_chars – how many characters you want to extract, starting from the right.
Notes
– If you don’t enter any value for [num_chars], the system assumes it means 0 if you entered =RIGHT(text,). The cell containing the result will be empty.
– If you don’t enter any value for [num_chars], the system assumes it means 1 if you entered =RIGHT(text).
Examples
In this example, we only entered the text argument: the result includes the first character of the cell, starting from the right.
In this example, we entered =RIGHT(text,): the system understood we meant 0 as the [num_chars] argument. The cell is empty.
In this example, we want the last 4 digits to appear as results. The formula we used is: =RIGHT(text,4).
What’s the RIGHT function formula in Google Sheets?
If you want to use the RIGHT function in Google Sheets, you can use a similar syntax than the one in Excel: =RIGHT(string, [number_of_characters]).
What is the MID function in Excel?
The MID function lets you extract characters from a text, starting from a specific position.
Syntax of the MID function: =MID(text,start_num,num_chars).
The function syntax has 3 arguments : text, start_num and num_chars; the last argument is optional.
* text – the cell containing the characters you want to extract.
* start_num – the starting position for the search to start from.
* num_chars – how many characters you want to extract, starting from the start_num position and from the left.
Notes
– If you don’t enter any value for num_chars, the system assumes it means 0 if you entered =MID(text,start_num,). The cell containing the result will be empty.
– We recommend entering all the values in the MID formula.
– If the num_chars argument is higher than the characters number of the text after the starting point, the system will include all the characters found from start_num.
Examples
In this example, we want to extract the size for each reference. Mentions of the size start from position 8 and the num_chars argument is different for each reference. We decide to use a high number for this argument so all the characters including in the text, starting from position 8, will be included.
For this example, we want to extract the 2 first numbers of each phone number without any prefix.
What’s the MID function formula in Google Spreadsheets?
If you want to use the MID function in Google Sheets, you can use a similar syntax than the one in Excel: =MID(string, starting_at, extract_length).
In summary, all these functions are really handy; you can also combine them with the SEARCH function in Excel. To learn how to use SEARCH in Excel, check our tutorial!
Don’t hesitate to rewatch our video at the top of the page for more examples!
