How to Extract Text Between Two Characters in Excel

Data manipulation is a fundamental skill for anyone working with spreadsheets. Frequently, datasets include structured strings—such as reference codes, serial numbers, or URLs—where specific information is sandwiched between special delimiters. Extracting this specific segment is crucial for clean data analysis.

This guide explores four efficient methods to extract text between two characters in Excel. Whether you prefer standard formulas, advanced string manipulation, case-insensitive searches, or automation via VBA, these step-by-step solutions will help you master string parsing.

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

Combining the LEFT, MID, and FIND functions is a classic approach to isolating data between identical delimiters (like slashes). This method isolates the text after the first instance of the character and strips away everything following the second instance.

To extract the text using this approach, follow these steps:

1. Select your target output cell (e.g., C5).
2. Enter the following combined formula:

=LEFT(MID(B5,FIND("/",B5)+1,LEN(B5)),FIND("/",MID(B5,FIND("/",B5)+1,LEN(B5)))-1)

3. Press Enter and drag the fill handle down to populate the remaining cells.

How Does the Formula Work?

• FIND("/",B5)+1: Locates the position of the first slash and shifts one character to the right to define the starting point of the desired substring.
• LEN(B5): Measures the total character length of the original string.
• MID(B5,FIND("/",B5)+1,LEN(B5)): Extracts all text starting immediately after the first slash through to the end of the string.
• FIND("/",MID(...))-1: Finds the position of the second slash within the newly isolated text string, determining exactly how many characters to keep.
• LEFT(...): Returns the precise string length required, cutting off the second delimiter and everything after it.

Method 2 – Combining SUBSTITUTE, MID, and REPT Functions

When dealing with variable string lengths or complex data structures, an alternative technique involves padding the string with spaces using SUBSTITUTE, MID, and REPT. This isolates the target block before cleaning up extra spaces.

To apply this technique:

1. Input this formula into your output cell C5:

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

2. Press Enter and copy the formula down using the fill handle.

How Does the Formula Work?

• REPT(" ",6): Appends extra spacing to prevent errors on short strings.
• SUBSTITUTE("/"&B5..., "/", REPT(",",255)): Replaces every occurrence of your target character (the slash) with 255 consecutive commas, expanding the distance between data segments.
• *`MID(…, 2255, 255)`**: Jumps directly to the second large block of padded characters, capturing the isolated segment along with surrounding commas.
• SUBSTITUTE(..., ",", ""): Removes all remaining commas from the extracted segment, leaving only the clean string.

Method 3 – Using MID and SEARCH Functions for Dynamic Extraction

If your text extraction requires a case-insensitive search or needs a straightforward syntax layout, the SEARCH function works identically to FIND, but offers flexibility across different Excel setups.

To use the SEARCH method:

1. Insert this formula into cell C5:

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

2. Press Enter and drag the formula down the column.

How Does the Formula Work?

• SEARCH("/",B5) + 1: Pinpoints the character index right after the first delimiter.
• SEARCH("/",B5,SEARCH("/",B5)+1): Finds the position of the second delimiter by instructing Excel to start searching after the first one.
• SEARCH(...) - SEARCH(...) - 1: Subtracts the first position from the second position to calculate the exact character length of the text inside.
• MID(...): Extracts the characters from the middle of the string based on those dynamic start and length parameters.

Method 4 – Using VBA to Extract Text Across Large Datasets

For large datasets, repetitive tasks, or complex workflows, using Visual Basic for Applications (VBA) macro is the most efficient choice. This automates the extraction across a designated cell range instantly.

Follow these steps to configure the macro:

1. Press Alt + F11 (or go to the Developer tab and click Visual Basic) to open the Visual Basic Editor.

2. Click Insert in the top menu and select Module.

3. Paste the following macro code into the blank module window:

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

4. Press F5 or click the Run Sub button on the toolbar.

The macro loops through your designated range and automatically writes the parsed text into the adjacent column.

Conclusion

Isolating text between specific characters in Excel can be accomplished through various approaches depending on your project needs. Standard string combinations like MID/LEFT/FIND and MID/SEARCH work beautifully for quick formulas. The padding method using SUBSTITUTE and REPT handles edge cases skillfully, while a VBA macro provides an automated solution for large datasets. Select the method that best aligns with your spreadsheet layout to keep your data cleaning workflow seamless and efficient.