Need to retrieve specific characters from a string in Excel? If so, in this guide, I’ll show you how to use the Excel string functions to obtain your desired characters within a string.
Specifically, I’ll review few examples to illustrate how to apply the following Excel string functions:
| Excel String Functions Used | Description of Operation |
| LEFT | Retrieve a specific number of characters from the left side of a string |
| RIGHT | Retrieve a specific number of characters from the right side of a string |
| MID | Get a specific number of characters from the middle of a string |
| LEFT, FIND | Get all characters before a symbol (for a varying-length string) |
| LEFT, FIND | Get all characters before space (for a varying-length string) |
| RIGHT, LEN, FIND | Obtain all characters after a symbol (for a varying-length string) |
| MID, FIND | Obtain all characters between two symbols (for a varying-length string) |
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 will 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 side of the string)
(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:
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 the 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 side of the string)
(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:
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 where the string is located, Starting position of the 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:
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, I’ll show you 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 desired symbol in quotations that you’d like to find, the cell where the string is located)
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:
While you used the dash symbol in the above example, the above formula would also work for other symbols, such as $, % and so on…
Just in case your were interested to write the above formula in MS Access, you can then use the following structure to get the same results (where “InStr” is used in Access instead of “FIND”):
LEFT([Table Name].[Field Name],InStr([Table Name].[Field Name],"-")-1)
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:
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:
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:
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.