Extracting text between parentheses in Excel can be a daunting task, especially when dealing with large datasets. However, there are several ways to achieve this using Excel formulas and functions. In this article, we will explore three methods to extract text between parentheses in Excel, including using the MID, FIND, and REGEX functions.
Method 1: Using the MID and FIND Functions
One way to extract text between parentheses is by using the MID and FIND functions in combination. The MID function returns a specified number of characters from a text string, while the FIND function returns the position of a specified character or text string within a text string.
Here's the formula:
=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)
Assuming the text string is in cell A1, this formula works as follows:
FIND("(",A1)finds the position of the opening parenthesis.FIND(")",A1)finds the position of the closing parenthesis.MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)extracts the text between the parentheses.
Example:
| Text String | Extracted Text |
|---|---|
| Hello (World) | World |
| Foo (Bar) Baz | Bar |
| (Example) Text | Example |
Method 2: Using the REGEX Function (Excel 2019 and later)
If you have Excel 2019 or later, you can use the REGEX function to extract text between parentheses. REGEX (regular expressions) is a powerful tool for matching patterns in text strings.
Here's the formula:
=REGEXEXTRACT(A1,"\((.*?)\)")
This formula works as follows:
\(matches an opening parenthesis.(.*?)captures any characters (non-greedy) between the parentheses.\)matches a closing parenthesis.
Example:
| Text String | Extracted Text |
|---|---|
| Hello (World) | World |
| Foo (Bar) Baz | Bar |
| (Example) Text | Example |
Method 3: Using Power Query (Excel 2010 and later)
Another way to extract text between parentheses is by using Power Query, a powerful data manipulation tool in Excel.
Here's the Power Query formula:
= Table.TransformColumns(#"Previous Step", {"Text", each Text.AfterDelimiter(_, "(", {0})})
This formula works as follows:
Text.AfterDelimiter(_, "(", {0})extracts the text after the first occurrence of the opening parenthesis.
Example:
| Text String | Extracted Text |
|---|---|
| Hello (World) | World |
| Foo (Bar) Baz | Bar |
| (Example) Text | Example |
Text Extraction Gallery
We hope this article has helped you learn three ways to extract text between parentheses in Excel. Whether you're using the MID and FIND functions, the REGEX function, or Power Query, you now have the tools to extract the text you need. Try out these methods and let us know which one works best for you!