Excel/Airtable formula: Extract multiple values enclosed in parentheses

Other Questions Airtable
1

Hey everyone! I’m stuck on a spreadsheet problem. I need to pull out all the text between parentheses from a cell and list them with commas. Here’s what I mean:

Cell A1 has:
pic1.png (/stuff/pic1.png), pic2.png (/stuff/pic2.png)

I want cell B1 to show:
/stuff/pic1.png, /stuff/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. I need it to work for any number of parentheses in the cell. Can anyone help me out with a formula for this? Thanks!

2

I’ve encountered a similar challenge in my work. While the FILTERXML approach is clever, it might not be available in all Excel versions. An alternative solution using more common functions could be:

=SUBSTITUTE(TEXTJOIN(“, “, TRUE, IFERROR(MID(A1, FIND(”(”, A1, ROW(INDIRECT(“1:”&LEN(A1)))), FIND(“)”, A1, ROW(INDIRECT(“1:”&LEN(A1)))) - FIND(“(”, A1, ROW(INDIRECT(“1:”&LEN(A1)))) - 1), “”)), “)”, “”)

This formula iterates through the cell content, extracting text between each set of parentheses. It’s complex but should work across different Excel versions. Test it out and see if it meets your needs.

3

I’ve wrestled with similar extraction challenges before. While the FILTERXML approach is nifty, it’s not always reliable across different Excel versions. Here’s a more universal solution I’ve found effective:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",CHAR(1)),")",CHAR(2))," ",""))

This formula replaces parentheses with unique characters, removes spaces, then:

=SUBSTITUTE(TEXTJOIN(", ",1,MID(SUBSTITUTE(A1,")",")|"),(FIND("|",$A$1,ROW($1:$99))+1),99)),CHAR(2),"")

This extracts the content between our unique characters and joins them.

Combine these for a robust solution that should work in most Excel versions. It’s saved me countless hours on similar tasks.

4

hey, i’ve dealt with this before! try using TEXTJOIN and FILTERXML. something like:

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

it’s a bit messy but should work for multiple parentheses. lmk if u need help!