Excel String Functions: LEFT, RIGHT, MID, LEN and FIND

Need to retrieve specific characters from a string in Excel? If so, in this guide, you’ll see how to use the Excel string functions to obtain your desired characters within a string.

Specifically, you’ll observe how to apply the following Excel string functions using practical examples:

Excel String Functions Used Description of Operation
LEFT Get characters from the left side of a string
RIGHT Get characters from the right side of a string
MID Get characters from the middle of a string
LEFT, FIND Get all characters before a symbol
LEFT, FIND Get all characters before a space
RIGHT, LEN, FIND Get all characters after a symbol
MID, FIND Get all characters between two symbols

Excel String Functions: LEFT, RIGHT, MID, LEN and FIND

To start, let’s say that you stored different strings in Excel. These strings may contain a mixture of:

  • Letters
  • Digits
  • Symbols (such as a dash symbol “-“)
  • Spaces

Now, let’s suppose that your goal is to isolate/retrieve only the digits within those strings.

How would you then achieve this goal using the Excel string functions?

Let’s dive into few examples to see how you can accomplish this goal.

Retrieve a specific number of characters from the left side of a string

In the following example, you’ll see three strings. Each of those strings would contain a total of 9 characters:

  • Five digits starting from the left side of the string
  • One dash symbol (“-“)
  • Three letters at the end of the string

As indicated before, the goal is to retrieve only the digits within the strings.

How would you do that in Excel?

Here are the steps:

(1) First, type/paste the table below into Excel, within the range of cells A1 to B4 (to keep things simple across all the examples to come, the tables to be typed/pasted into Excel, should be stored in the range of cells A1 to B4):

Identifier Result
55555-End
77777-End
99999-End

Since the goal is to retrieve the first 5 digits from the left, you’ll need to use the LEFT formula, which has the following structure:

=LEFT(Cell where the string is located, Number of characters needed from the Left)

(2) Next, type the following formula in cell B2:

=LEFT(A2,5)

(3) Finally, drag the LEFT formula from cell B2 to B4 in order to get the results across your 3 records.

This is how your table would look like in Excel after applying the LEFT formula:

Identifier Result
55555-End 55555
77777-End 77777
99999-End 99999

Retrieve a specific number of characters from the right side of a string

Wait a minute! what if your digits are located on the right-side of the string?

Let’s look at the opposite case, where you have your digits on the right-side of a string.

Here are the steps that you’ll need to follow in order to retrieve those digits:

(1) Type/paste the following table into cells A1 to B4:

Identifier Result
ID-55555
ID-77777
ID-99999

Here, you’ll need to use the RIGHT formula that has the following structure:

=RIGHT(Cell where the string is located, Number of characters needed from the Right)

(2) Then, type the following formula in cell B2:

=RIGHT(A2,5)

(3) Finally, drag your RIGHT formula from cell B2 to B4.

This is how the table would look like after applying the RIGHT formula:

Identifier Result
ID-55555 55555
ID-77777 77777
ID-99999 99999

Get a specific number of characters from the middle of a string

So far you have seen cases where the digits are located either on the left-side, or the right-side, of a string.

But what if the digits are located in the middle of the string, and you’d like to retrieve only those digits?

Here are the steps that you can apply:

(1) Type/paste the following table into cells A1 to B4:

Identifier Result
ID-55555-End
ID-77777-End
ID-99999-End

Here, you’ll need to use the MID formula with the following structure:

=MID(Cell of string, Start position of first character needed, Number of characters needed)

(2) Now type the following formula in cell B2:

=MID(A2,4,5)

(3) Finally, drag the MID formula from cell B2 to B4.

This is how the table would look like:

Identifier Result
ID-55555-End 55555
ID-77777-End 77777
ID-99999-End 99999

In the subsequent sections, you’ll see how to retrieve your desired characters from strings of varying lengths.

Gel all characters before a symbol (for a varying-length string)

Ready to get more fancy?

Let’s say that you have your desired digits on the left side of a string, BUT the number of digits on the left side of the string keeps changing.

In the following example, you’ll see how to retrieve all the desired digits before a symbol (e.g., the dash symbol “-“) for a varying-length string.

For that, you’ll need to use the FIND function to find your symbol.

Here is the structure of the FIND function:

=FIND(the symbol in quotations that you'd like to find, the cell of the string)

Now let’s look at the steps to get all of your characters before the dash symbol:

(1) First, type/paste the following table into cells A1 to B4:

Identifier Result
111-IDAA
2222222-IDB
33-IDCCC

(2) Then, type the following formula in cell B2:

=LEFT(A2,FIND("-",A2)-1)

Note that the “-1” at the end of the formula simply drops the dash symbol from your results (as we are only interested to keep the digits on the left without the dash symbol).

(3) As before, drag your formula from cell B2 to B4. Here are the results:

Identifier Result
111-IDAA 111
2222222-IDB 2222222
33-IDCCC 33

While you used the dash symbol in the above example, the above formula would also work for other symbols, such as $, % and so on.

Gel all characters before space (for a varying-length string )

But what if you have a space (rather than a symbol), and you only want to get all the characters before that space?

That would require a small modification to the formula you saw in the last section.

Specifically, instead of putting the dash symbol in the FIND function, simply leave an empty space within the quotations:

FIND(" ",A2)

Let’s look at the full steps:

(1) To start, type/paste the following table into cells A1 to B4:

Identifier Result
111 IDAA
2222222 IDB
33 IDCCC

(2) Then, type the following formula in cell B2:

=LEFT(A2,FIND(" ",A2)-1)

(3) Finally, drag the formula from cell B2 to B4:

Identifier Result
111 IDAA 111
2222222 IDB 2222222
33 IDCCC 33

Obtain all characters after a symbol (for a varying-length string )

There may be cases where you may need to get all of your desired characters after a symbol (for a varying-length string).

To do that, you may use the LEN function, which can provide you the total number of characters within a string. Here is the structure of the LEN function:

=LEN(Cell where the string is located)

Let’s now review the steps to get all the digits, after the symbol of “-“, for varying-length strings:

(1) First, type/paste the following table into cells A1 to B4:

Identifier Result
IDAA-111
IDB-2222222
IDCCC-33

(2) Secondly, type the following formula in cell B2:

=RIGHT(A2,LEN(A2)-FIND("-",A2))

(3) Finally, drag your formula from cell B2 to B4:

Identifier Result
IDAA-111 111
IDB-2222222 2222222
IDCCC-33 33

Obtain all characters between two symbols (for a varying-length string)

Last, but not least, is a scenario where you may need to get all of your desired characters between two symbols (for a varying-length string).

In order to accomplish this task, you can apply a mix of some of the concepts we already covered earlier.

Let’s now look at the steps to retrieve only the digits between the two symbols of dash “-“:

(1) First, type/paste the following table into cells A1 to B4:

Identifier Result
IDAA-111-AA
IDB-2222222-B
IDCCC-33-CCC

(2) Then, type the following formula in cell B2:

=MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1)

(3) And finally, drag your formula from cell B2 to B4:

Identifier Result
IDAA-111-AA 111
IDB-2222222-B 2222222
IDCCC-33-CCC 33

Excel String Functions – Summary

Excel string functions can be used to retrieve specific characters within a string.

You just saw how to apply Excel string functions across multiple scenarios. You can use any of the concepts above, or a mixture of the techniques described, in order to get your desired characters within a string.