Microsoft has been in use for a long time to create grids of texts. It is essentially a spreadsheet program which is the reason why it's so successful in creating texts, and numbers and having specific calculations through excel formulas. Its functionality is very useful for both individuals and businesses. Businesses use Excel to keep records and record expenditures. It can easily be used to chart data, plan a budget, and eventually provision fiscal results.
Although excel is highly valuable and part of us today, many people don't know how to use it right. This is actually the reason why we are here to explain different excel functionalities to make your work easier. Today, we are going to discuss how to extract texts from a string between characters.
If you have many strings in excel and now you want to extract part of it between two characters, there's a way you can handle this as quickly as possible. An example of these excel strings includes a list of email addresses. Now assume you have a list of these email addresses and you want to extract an email address to an additional column for convenience. How do you go about that?
Using a Formula
Here is another method that you can use to extract text from a string between characters. To do this, you can just follow the below steps.
Start with selecting a cell where you will paste the results.
Now paste this formula to the cell: =MID(LEFT(A1,FIND(">",A1)-1), FIND("<",A1)+1, LEN(A1), and afterwards press the enter Key.
You can drag the Autofill handle downwards if you want to apply the formula to other cells.
After this, you have to understand the meaning of different players in the formula:
A3 is the text cell
<&> are the characters we want to extract between.
Use combined formulas
This formula is used when there are two same characters involved. This is how you can go about it;
First and foremost select the cell in which you will paste the results.
After that type this formula into that particular cell: =SUBSTITUTE(MID(SUBSTITUTE("/"&A3&REPT(" ",6),"/REPT(","255)),2*255,255),",",""), and then press the enter key on the keyboard.
There is the option of dragging the Autofill handle down towards the other cells to apply this formula.
Keep in mind there are some specific characters that are used in the formula. For instance;
A3 is the text cell.
/ is specifically that character you want to extract between strings.
Using MID and SEARCH Functions
The SEARCH function in the combination formula returns the number of characters at which you find the specific character or text string. To use the combination formula to extract text from a string of characters, you can follow these steps:
1. Suppose the text string is found in cell B5, you can write the following formula in cell C5:
=MID(B5, SEARCH("/",B5) + 1, SEARCH("/",B5,SEARCH("/",B5)+1) – SEARCH("/",B5) – 1), where;
The SEARCH(“/”,B5) + 1 function will return the number of characters at which a specific character or text string is first found, such as {5;7;5;5;5;5}. The function is also used to start characters for the MID function.
The SEARCH(“/”,B5,SEARCH(“/”,B5)+1) – SEARCH(“/”,B5) – 1 function returns the {4;2;5;4;3;3} and is used to end characters for the MID function.
The MID function extracts text or characters from the middle of the string.
2. Press the Enter button and the formula will return an extracted text in cell C5.
3. You can use the Fill Handle to drag the formula down the column and generate extracted texts in the rest of the cells.
Using VBA Code
The VBA code is also among the developer options you can use to extract text between two characters. To do this, you can follow these steps:
1. Go to the Developer tab and select the Visual Basic option.
2. Open Visual Basic Editor. You can also press the Alt + F11 keys altogether to open the Visual Basic Editor.
3. On the newly opened window, click Insert and select Module.
4. Copy the following code and paste it into the opened box:
Sub Extract_text_between_two_characters()
Dim first_postion As Integer
Dim second_postion As Integer
Dim cell, rng As Range
Dim search_char As String
Set rng = Range("B5:B10")
For Each cell In rng
search_char = "/"
first_postion = InStr(1, cell, search_char)
second_postion = InStr(first_postion + 1, cell, search_char)
cell.Offset(0, 1) = Mid(cell, first_postion + 1, second_postion – first_postion – 1)
Next cell
End Sub
Code Source; click here
5. Next, press the F5 button to run the code. Alternatively, you can select the Run option and select Run Sub/UserFrom.
6. The operation will return a new text extracted from the original string.
Using Kutools for Excel
Kutools for Excel is an add-on that allows you to perform almost 300 Excel operations. However, the feature is not found in all Excel versions. That means, if your Excel version does not have Kutools for Excel, you can get it from their official website and install it easily by following these steps:
- You will need to close all Excel files and Office applications before downloading Kutools for Excel.
- After downloading, follow the installation steps as indicated on the screen and click the Finish button to complete the process.
- You can now open your Excel worksheet and you will see Kutools in the Menu bar.
After installing the add-on, you can use it to extract a text from a string between characters as follows:
1. Select the cell in which you want the extracted text to appear.
2. Click on the Kutools tab and select the Formula option.
3. Next, click on the Formula Helper option and a new dialogue box will open.
4. In the Filter checkbox, type ex, and all extraction formulas will appear in the Choose a formula section.
5. Select Extract strings between specified text. In the Arguments input section, select the cell from which you want to extract a text or substring and fill it in the Cell checkbox.
6. You can now type the two characters or texts you want to extract between the Start char(s) and End char(s) checkboxes.
7. Finish by clicking OK and you will see the extracted text in the selected cell. You can also use the Fill Handle to drag down and extract a text from each string.
The related functions
There are some functions that are commonly used in the two formulas. They include;
The Excel MID function
The MID function basically returns a substring from a text string at any position that you may have specified. That's why it's found in both formulas. The syntax of this function is: =MID (text, start_num, num_chars).
The Excel SEARCH function
The SEARCH function is used to return the number of the starting location of the substring in a text string. It has a syntax which goes as: =SEARCH (find_text, within_text,[start_num]).
The Excel LEN function
The LEN function works in the formula to return the length of a text string. These are the number of characters that can be found within that particular text string. This is usually a built-in function in Excel and it's considered to be a Text function. Its syntax is: LEN(text).
Useful takeaway
When you are using Excel to extract text from a string between characters, be careful and ensure you have followed every step, failure to do so, you will fail to get the right results.