How to Extract Text Between Two Characters in Excel?

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for some special tricks to extract text between two characters in Excel, you’ve come to the right place. In Microsoft Excel, there are numerous ways to extract text between two characters. In this article, we’ll discuss four methods to extract text between two characters. Let’s follow the complete guide to learn all of this.


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

Here, we have a dataset containing Reference and Client Code. Our main goal is to extract text between two characters.

In the following section, we will use 4 methods to extract text between two characters.

Read More: How to Extract Text Before Character in Excel


1. Using MID, LEFT, and FIND Functions to Extract Text

To extract text, we will combine the MID function, the LEFT function, and the FIND function. Here, the MID function returns the characters from the middle of a text string. The LEFT Function returns the specified number of characters from the start of a text string. Finally, the FIND function returns the starting position of one text string. You have to follow the following steps to extract text in the Client Code column in the below dataset.

Using MID, LEFT, and FIND Functions to Extract Text

We will use 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)

After that, 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


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

To extract text in the Client Code Column, we will combine the SUBSTITUTE function, the MID function, and the REPT function. Here, the SUBSTITUTE function replaces existing text with new text in a text string, and the REPT function repeats text a given number of times.

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

We will use the following combined formula in the output cell C5:

=SUBSTITUTE(MID(SUBSTITUTE("/"&B5&REPT(" ",6),"/",REPT(",",255)),2*255,255),",","")

After that. 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


3. Using MID and SEARCH Functions to Extract Text

To extract text in the Client code column, we have to combine the MID function and the SEARCH function. Here, the SEARCH function returns the number of characters at which a specific character or text string is first found.

Using MID and SEARCH Functions to Extract Text

We will use the following combined formula in the output cell C5:

=MID(B5, SEARCH("/",B5) + 1, SEARCH("/",B5,SEARCH("/",B5)+1) - SEARCH("/",B5) - 1)

After that, press Enter and drag the fill handle. Now you will get the following output.

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


4. Using VBA to Extract Text Between Two Characters in Excel

Now, you have to follow the following steps if you want to extract text in the Client Code column.

Using VBA to Extract Text Between Two Characters in Excel

📌 Steps:

  • Firstly, press ALT+F11 or you have to go to the tab Developer, select Visual Basic to open Visual Basic Editor, and click Insert, select Module.

Using VBA to Extract Text Between Two Characters in Excel

  • Next, you have to type 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
  • Now, 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


💬 Things to Remember

✎ If you are using the combined large formula, you should carefully use the parentheses.


Download Practice Workbook

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


Conclusion

That’s the end of today’s session. I strongly believe that from now you may extract text between the two characters in Excel. If you have any queries or recommendations, please share them in the comments section below.


Related Readings

Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Welcome to my Profile. I am working on and researching Microsoft Excel right now, and I will be posting articles about it here. I received a B.Sc. in Naval Architecture and Marine Engineering from the Bangladesh University of Engineering and Technology (BUET). Having studied naval architecture, I have a strong interest in research and development. Always try to learn from different sources and come up with creative solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo