Excel extract text in brackets

This Excel tutorial explains how to extract text in brackets or in defined text in Excel spreadsheet and VBA.

Extract text in brackets in Excel spreadsheet

In the previous post, I demonstrated how to remove text in brackets or in any defined text, in this post I will explain how to extract the text in brackets or in any defined text.

In Excel spreadsheet, you can extract any substring using Mid Function.

To recap the syntax of Mid Function

MID( text, start_position, number_of_characters )
text The text string from which you want to extract the characters
start_position The position of the first character you want to extract. Positions start at 1.

number_of_character

The number of characters to return.

Looking at the function arguments, the question is where do we start and end the extraction.

Suppose Cell A1 contains text ABC (D E F)

If we want to extract the text inside brackets (i.e. D E F), in the first argument (start position), we locate the position of open bracket using Search Function, and then add 1 so that we begin extracting without including open bracket.

SEARCH("(",A1)+1

For the second argument, the number of characters is calculated by deducting the position of open bracket from position of close bracket, and then minus one to exclude close bracket.

SEARCH(")",A1)-SEARCH("(",A1)-1

The complete formula would be

=MID(A1,SEARCH("(",A1)+1,SEARCH(")",A1)-SEARCH("(",A1)-1)

Examples

Note that this formula just work on the first set of brackets, it does not work for the 2nd and more brackets.

Second, this formula doesn’t just work on brackets, you can define any starting text and ending text in the Search Function argument.

Extract text in brackets in VBA

The way of extraction is same in VBA, except that Find Function is not available in VBA, we need to use Instr Function instead.

The syntax of Instr Function is slightly different from Find Function.

InStr( [start], string, substring, [compare] )

Use the below code to extract the text in brackets in cell A1

Mid(Range("A1"), InStr(Range("A1"), "(") + 1, InStr(Range("A1"), ")") - InStr(Range("A1"), "(") - 1)

Extract text in brackets using VBA custom Function

I personally don’t like using long formula because it is too easy to make mistakes in the arguments when it get too long. Using custom Function can simplfy the arguments.

VBA Code of custom Function

Press ALT+F11 and then insert the below code in a Module.

Public Function wExtractStr(myStr, stStr, edStr)
    wExtractStr = Mid(myStr, InStr(myStr, stStr) + 1, InStr(myStr, edStr) - InStr(myStr, stStr) - 1)
End Function

Function Syntax

wExtractStr(myStr, stStr, edStr)
myStr The text that you want to extract from
stStr The previous text that you want to begin extraction
edStr The next text that you want to end extraction

Example

 

 

 

Leave a Reply

Your email address will not be published.