Excel/Airtable formula: Extract multiple values from parentheses

Other Questions Airtable
1

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

In cell A1, I’ve got:
picture1.jpg (/folder/pic1.jpg), picture2.jpg (/folder/pic2.jpg)

I want cell B1 to show:
/folder/pic1.jpg, /folder/pic2.jpg

I figured out how to grab one value with this:
MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)

But it only gets the first one. I need it to work for all parentheses in the cell, and the number of parentheses changes in each row. Any ideas on how to make this work? Thanks!

2

I’ve encountered a similar challenge before. One approach that might work for you is using a combination of regular expressions and array formulas. Here’s a potential solution:

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

This formula iterates through the cell content, extracting all text within parentheses. It’s quite complex, but it should handle multiple instances and varying numbers of parentheses in each row. You might need to adjust it slightly depending on your specific Excel version or if you’re using Airtable.

3

hey ameliat, I’ve run into this before! u could try a combo of TEXTJOIN and FILTERXML. somethin like:

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

might look scary but it works like magic for multiple parentheses. good luck!

4

I’ve dealt with similar data extraction challenges in my work. One approach that’s worked well for me is using a custom VBA function. Here’s a simplified version:

Function ExtractParentheses(cellValue As String) As String
Dim regex As Object
Set regex = CreateObject(“VBScript.RegExp”)
regex.Global = True
regex.Pattern = “((.*?))”

Dim matches As Object
Set matches = regex.Execute(cellValue)

Dim result As String
For Each match In matches
    result = result & IIf(Len(result) > 0, ", ", "") & match.SubMatches(0)
Next

ExtractParentheses = result

End Function

You’d call this function in your cell like =ExtractParentheses(A1). It’s more flexible than formula-based solutions and handles multiple parentheses easily. Just remember to enable the VBA editor in Excel first.