Here is a dataset containing Reference and Client Code. Let’s extract the text between two slashes.
Read More: How to Extract Text Before Character in Excel
Method 1 – Using MID, LEFT, and FIND Functions to Extract Text
- Put the following combined formula in the output cell C5:
=LEFT(MID(B5,FIND("/",B5)+1,LEN(B5)),FIND("/",MID(B5,FIND("/",B5)+1,LEN(B5)))-1)
- Press Enter and drag the fill handle. Now you will get the following output.
How Does the Formula Work?
- Here, the FIND(“/”,B5)+1 function returns the starting position of one text string that we want to extract between two characters and we will get the following output:
{5;7;5;5;5;5}
- The LEN(B5) function returns the number of characters in a text string like the following:
{11;11;13;12;10;10}
- Here, the MID(B5,FIND(“/”,B5)+1,LEN(B5)) will return the texts after the first character like the following output:
{THER/38 ;GS/31; XLMNE/846; ENHT/846; TML/23; KGF/14}
- The FIND(“/”,MID(B5,FIND(“/”,B5)+1,LEN(B5)))-1 will return the length of the text( what get from the above function) that we want to extract and show the following output:
{4;2;5;4;3;3}
- Finally, by using the LEFT function we will get the specified number of text between two characters.
Read More: How to Extract Text after a Specific Text in Excel
Method 2 – Combining SUBSTITUTE, MID, and REPT Functions to Extract Text Between Two Characters in Excel
Let’s change the dataset a bit, but still use slashes to extract text.
- Put the following combined formula in the output cell C5:
=SUBSTITUTE(MID(SUBSTITUTE("/"&B5&REPT(" ",6),"/",REPT(",",255)),2*255,255),",","")
- Press Enter and drag the fill handle. Now you will get the following output.
How Does the Formula Work?
- Here, the REPT(” “,6)REPT function repeats text a given number of times.
- The MID(SUBSTITUTE(“/”&B5&REPT(” “,6),”/”,REPT(“,”,255)),2*255,255) will return the following output in cell C5:
{,,,Nancy,,,,,,,,,,,,..}
- Then the SUBSTITUTE function will return the following output:
{Nancy;GS;XLMNE;ENHT;TML;KGF}
Read More: How to Extract Text After First Space in Excel
Method 3 – Using MID and SEARCH Functions to Extract Text
The SEARCH function returns the number of characters at which a specific character or text string is first found, so we’ll use it to find the slashes.
- Copy the following formula in the output cell C5:
=MID(B5, SEARCH("/",B5) + 1, SEARCH("/",B5,SEARCH("/",B5)+1) - SEARCH("/",B5) - 1)
- Press Enter and drag down the fill handle. This fills the rest of the column.
How Does the Formula Work?
- Here, the SEARCH(“/”,B5) + 1 function returns the number of characters at which a specific character or text string is first found like the following:
{5;7;5;5;5;5}
This is used for starting characters for the MID function.
- The SEARCH(“/”,B5,SEARCH(“/”,B5)+1) – SEARCH(“/”,B5) – 1 function return the following output:
{4;2;5;4;3;3}
This is the ending character for the MID function.
- Finally, the MID function returns the characters from the middle of a text string.
Similar Readings
- How to Extract Text after Second Space in Excel
- How to Extract Text After Last Space in Excel
- How to Extract Text Between Two Commas in Excel
Method 4 – Using VBA to Extract Text Between Two Characters in Excel
Start with the same dataset.
- Press Alt+F11 or go to the Developer tab, select Visual Basic to open Visual Basic Editor, and click Insert, select Module.
- Copy the following code:
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
- Press F5 or select Run, and click on Run Sub/UserFrom.
Finally, you will get the following required output.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Related Readings
- How to Extract Text after Second Comma in Excel
- How to Extract Text between Two Spaces in Excel
- How to Extract Certain Text from a Cell in Excel VBA
- How to Extract Text After a Character in Excel
<< Go Back to Extract Text in Excel | String Manipulation | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!