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.|
|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.
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.
The complete formula would be
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
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|