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.

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.

Similar Readings

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

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

Method 1 – Using MID, LEFT, and FIND Functions to Extract Text

Method 2 – Combining SUBSTITUTE, MID, and REPT Functions to Extract Text Between Two Characters in Excel

Method 3 – Using MID and SEARCH Functions to Extract Text

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

Related Readings

Leave a reply Cancel reply