Hey everyone! I’m struggling with a spreadsheet formula. I need to pull out all the text between parentheses from a cell. Here’s what I mean:
In cell A1, I’ve got something like:
picture1.png (/folder1/pic1.png), picture2.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,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)
But this only grabs the first set of parentheses. I need it to work for all of them, and the number of items in parentheses changes in each row.
Any ideas on how to make this work? Thanks in advance for your help!
I’ve run into this issue before and found a workaround using Power Query. What I did was first go to Data > Get & Transform Data > From Table/Range, select the data and click ‘OK’. In the Query Editor, I added a custom column by going to Add Column > Custom Column, naming the column, and entering a formula that extracts all text between the parentheses using a combination of Text.Select, Text.Split, and Text.Combine functions. This approach extracts each segment within the parentheses and then combines them with commas, handling multiple sets efficiently. After completing these steps, I closed and loaded the query back into the sheet. Hope this helps if you run into a similar challenge.
I’ve dealt with similar issues before, and I found that using a combination of array formulas can be quite effective. Here’s a solution that might work for you:
=TEXTJOIN(“, “, TRUE, TRIM(MID(SUBSTITUTE(A1, “)”, “)^^”), SEARCH(”(^”, SUBSTITUTE(A1, “(”, “^(”, SEQUENCE(LEN(A1)-LEN(SUBSTITUTE(A1,“(”,“”))))), SEARCH(“)”, A1&“)”, SEARCH(“(^”, SUBSTITUTE(A1, “(”, “^(”, SEQUENCE(LEN(A1)-LEN(SUBSTITUTE(A1,“(”,“”)))))-SEARCH(“(^”, SUBSTITUTE(A1, “(”, “^(”, SEQUENCE(LEN(A1)-LEN(SUBSTITUTE(A1,“(”,“”))))))))
This formula uses SUBSTITUTE to mark the positions of parentheses, then uses SEARCH and MID to extract the content between them. It’s a bit complex, but it should handle multiple parentheses in a single cell. You might need to adjust it slightly depending on your Excel version.
hey there! have u tried using a regex formula? something like this might work:
=TEXTJOIN(“, “, TRUE, IFERROR(MID(A1, FIND(”(”, A1, ROW(INDIRECT(“1:” & LEN(A1)))), FIND(“)”, A1, FIND(“(”, A1, ROW(INDIRECT(“1:” & LEN(A1))))) - FIND(“(”, A1, ROW(INDIRECT(“1:” & LEN(A1)))) - 1), “”))
it’s a bit complicated but should grab all the stuff in parentheses. good luck!