Excel/Airtable formula to extract multiple values within parentheses

Other Questions Airtable
1

I need help with a spreadsheet formula. My goal is to pull out all the text inside parentheses from a cell with multiple entries. Here’s what I mean:

In cell A1, I have something like this:
image1.png (/folder1/pic1.png), image2.png (/folder2/pic2.png)

I want cell B1 to show:
/folder1/pic1.png, /folder2/pic2.png

I figured out how to get one value:
=MID(A1,SEARCH("(",A1)+1,SEARCH(")",A1)-SEARCH("(",A1)-1)

But this only grabs the first set of parentheses. How can I make it work for all sets in the cell? The number of entries might change from row to row.

Any ideas on how to do this in Excel or Airtable? Thanks!

2

hey, i’ve got a quick fix for ya. try this in excel:

=TEXTJOIN(“, “, TRUE, IF(ISNUMBER(SEARCH(”()", SUBSTITUTE(A1, " ", "”))), MID(SUBSTITUTE(A1, " “, ““), SEARCH(”(", SUBSTITUTE(A1, " ", "”))+1, SEARCH(”)", SUBSTITUTE(A1, " ", ““))-SEARCH(”(", SUBSTITUTE(A1, " ", "”))-1), “”))

it’s a bit messy but works like a charm for multiple parentheses. good luck!

3

I’ve tackled a similar problem before, and here’s a solution that worked for me in Excel:

=TEXTJOIN(“, “, TRUE, FILTERXML(””&SUBSTITUTE(SUBSTITUTE(A1,“(”,“”),“)”,“”)&“”, “//m”))

This formula uses FILTERXML to extract all text within parentheses and TEXTJOIN to combine the results. It’s quite robust and handles multiple entries well.

For Airtable, you might need to use a custom script or multiple formula columns, as it doesn’t have direct equivalents for these Excel functions. Consider using a script that leverages regular expressions for a more flexible solution in Airtable.

4

I’ve been working with Excel formulas for years, and I’ve found a neat trick for this kind of task. Try this array formula:

=IFERROR(TRANSPOSE(FILTERXML(“” & SUBSTITUTE(SUBSTITUTE(A1, “(”, “”), “)”, “”), “”) & “”, “//b[.!=‘’]”)), “”)

You’ll need to enter it as an array formula (Ctrl+Shift+Enter in older Excel versions). This approach uses FILTERXML to extract all the text within parentheses, even if there are multiple instances.

It’s been pretty reliable for me across different datasets. Just make sure your data is consistently formatted. If you’re working with large datasets, you might notice it’s a bit slower than simpler formulas, but the flexibility is worth it in my experience.