In this tutorial, I will show you examples of using the functions to get substrings in Excel. The Excel has a few functions like LEFT, MID, and RIGHT that can be used for getting the substrings from the given text, specified cell etc.
Excel LEFT function
For returning the first character or desired number of characters from the left of given text string, you may use the LEFT function.
The syntax of using the LEFT function is:
- Text is the required argument. This is the text from where a substring will be returned. You may use cell reference or constant literal.
- The num_chars is an optional argument. This specifies the number of characters to return from the left of given text string.
- If num_chars is omitted, the default is 1. That is, one character from the left is returned.
- The num_chars must be greater than 1. If it exceeds the total length of text, it returns the complete text string.
The example getting substring by LEFT function
In the first example of the LEFT function, I am using a text string in LEFT function and provide a number to get a substring. The LEFT formula:
1 =LEFT("LEFT Function Example",13)
I applied this formula in the A3 cell and it returned “LEFT Function” substring.
What if num_char was omitted?
See the same example as above and this time I will not use the second argument i.e. num_chars. See the result and formula below:
1 =LEFT("LEFT Function Example")
The above formula only returned one character from the left i.e. “L”.
Using cell reference in the LEFT function
This time, I will use the cell reference (A3) in the LEFT function for the text argument. The returned substring will display in the B3 cell for num_char=10.
You may also use cell reference for the num_char argument. For example:
In that case, enter the number in the A4 cell for getting the substring from A3 cell.
Extract the substring by Excel MID function
The MID function can be used to extract the substring in Excel by providing the text string, starting number and number of characters to return.
The general syntax of MID function is:
MID(text, start_num, num_chars)
An example of MID function to extract substring
I will use the text in MID function with start and number of characters arguments as follows:
The MID formula:
=MID(“Extract the text by MID”,9,8)
In the result, you can see the MID function started extracting the substring from the 9th character which is “t” and moved towards right till 8 characters.
Dynamically using MID function example
For this example, I will use cells for all the arguments in MID function for extracting a substring.
So, we are taking the source text from B2 cell. The start number is taken from the B3 cell while the B4 is used for the number of characters from the start number to return by MID function.
The RIGHT function in Excel to get substrings
The RIGHT function returns the substring from the right most of the text string. It also takes two arguments i.e.
If you omit the num_charc argument, the RIGHT returns one character from the right of the text.
An example of RIGHT function
The following example gets the substring by using RIGHT function with both parameters.
You can count and see it returned 9 characters that started from the right of the text (given in B2 cell).
If you do not provide num_chars argument then see the result by using this formula:
You see, it only returned the last character i.e. ‘?’.