# How to Extract Text Between Two Characters in Excel: 4 Methods

Here is a dataset containing Reference and Client Code. Let’s extract the text between two slashes.

### 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}

### 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.

### Method 4 – Using VBA to Extract Text Between Two Characters in Excel

• 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.

<< Go Back to Extract Text in Excel | String Manipulation | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!