VBA: How to Extract Text Between Two Characters

You can create the following custom function in VBA to extract the text between two specific characters:

Function ExtractBetween(this_text, start_char, end_char)

StartPosition = InStr(this_text, start_char)
EndPosition = InStr(this_text, end_char)

ExtractBetween = Mid(this_text, StartPosition + 1, EndPosition - StartPosition - 1)

End Function

You can then use this function to extract the text between any two specific characters within a specific character in Excel.

The following example shows how to use this function in practice.

Example: Use VBA to Extract Text Between Two Characters

Suppose we have the following dataset in Excel that contains ID values and total sales for various products at some company:

Suppose we would like to use VBA to extract the text between the parenthesis for each cell in column A and return the results in column C.

We can create the following function to do so:

Function ExtractBetween(this_text, start_char, end_char)

StartPosition = InStr(this_text, start_char)
EndPosition = InStr(this_text, end_char)

ExtractBetween = Mid(this_text, StartPosition + 1, EndPosition - StartPosition - 1)

End Function

We can then type the following formula into cell C2 to extract the text between the parenthesis for the text in cell A2:

=ExtractBetween(A2, "(", ")")

We can then click and drag this formula down to each remaining cell in column C:

VBA extract text between two specific characters

Column C now displays the text between the parenthesis for each corresponding cell in column A.

Note: This formula works by using the Instr function in VBA to extract the position of the specific starting and ending characters, then by using the Mid function to return all text between these two characters.