Get Excel Substring by Left, Mid and Right functions

In this tutorial, I will show you examples of using the functions to get substrings in Excel.

Excel has a few functions like LEFT, MID, and RIGHT that can be used to get the substrings from the given text, specified cell, etc.

Excel LEFT function

To return the first character or the 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:

LEFT(text, [num_chars])

Where:

Text

Text is the required argument. This is the text from where a substring will be returned. You may use cell reference or constant literal.

num_chars

The num_chars is an optional argument. This specifies the number of characters to return from the left of a given text string.

The num_chars must be greater than 1.
If it exceeds the total length of the text, it returns the complete text string.

num_chars is omitted

If num_chars is omitted, the default 1 is used. That is, one character from the left is returned.

The example of getting substring by LEFT function

In the first example of the LEFT function, I am using a text string in the LEFT function and providing a number to get a substring. The LEFT formula:

=LEFT(“LEFT Function Example”,13)

The result:

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:

=LEFT(“LEFT Function Example”)

The result:

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.

=LEFT(A3,10)

You may also use a cell reference for the num_char argument. For example:

=LEFT(A3,A4)

In that case, enter the number in the A4 cell to get 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 the MID function is:

MID(text, start_num, num_chars)

An example of MID function to extract the substring

I will use the text in the MID function with start and number of characters arguments as follows:

The MID formula:

=MID(“Extract the text by MID”,9,8)

The result:

In the result, you can see the MID function started extracting the substring from the 9^th 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 the MID function for extracting a substring.

The Formula:

=MID(B2,B3,B4)

So, we are taking the source text from the 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.

Did you know: Excel has LEFTB, MIDB, and RIGHTB functions that count each double-byte character as 2 only if DBCS language is set as default. The DBCS languages include Japanese, Chinese (Simplified/Traditional), and Korean.

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.

RIGHT(text,[num_chars])

If you omit the num_charc argument, the RIGHT returns one character from the right of the text.

An example of the RIGHT function

The following example gets the substring by using RIGHT function with both parameters.

The formula:

=RIGHT(B2,B3)

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:

=RIGHT(B2)

You see, it only returned the last character i.e. ‘?’.