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.

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.

Using MID, LEFT, and FIND Functions to Extract Text

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.

SUBSTITUTE, MID, and REPT Functions to Extract Text Between Two Characters in Excel

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

Using MID and SEARCH Functions to Extract Text

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

Using MID and SEARCH Functions to Extract Text

  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.

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.

Using VBA to Extract Text Between Two Characters in Excel

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

Using VBA to Extract Text Between Two Characters in Excel


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Related Readings


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

Get FREE Advanced Excel Exercises with Solutions!
Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo