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.