Extract Multiple Sub-strings Between Brackets/Parentheses in Excel

Question

I have various strings (formatted as text) that have a similar syntax in column F. Eg:

1t ttn TEST FAILED (9-5 passOne, 21-7 & 877-12 passTwo)

I want to extract the numbers within the brackets into separate cells. There may be anywhere between 1 and 5 different numbers.

The numbers will always contain a dash (-) somewhere in the middle and may have up to 3 digits either side of the dash.

Is this possible? I've been through previous questions and can't find anything that answers this.

Edit to show entered JvdV formula:

Would there ever by numbers containing a hyphen (-) outside the brackets? And yes it's possible, have you tried anything that gets you started? Sidenote, please include your version of Excel. — JvdV, May 28 '21 at 10:12
So result will contain this hyphen? From your sample data the result will be `9-5`, `21-7`, `877-12`? — Harun24hr, May 28 '21 at 10:18
Hi Harun, yes with each of those appearing in a different cell. Eg D1 = 9-5 D2 = 21-7 D3 = 877-12 — Robsmith, May 28 '21 at 10:19
Another question; would the part that is inside brackets always at the end of the string? — JvdV, May 28 '21 at 10:21

JvdV · Answer 1 · 2021-05-28T10:38:12.973

3

With Excel 2016:

Formula in B1:

=SUBSTITUTE(FILTERXML("<t><s>`"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPLACE($A1,1,FIND("(",$A1),""),")",""),"&","&")," ","</s><s>`")&"</s></t>","//s[contains(., '-')][translate(., '`-','')*0=0]["&COLUMN(A1)&"]"),"`","")

Confirm through CtrlShiftEnter

Drag right, and down for all lines of data.

This assumed:

Substring of interest (between paranthesis) is always at the end.
Numbers with the hyphen as a delimiter may occur outside this substring and are not wanted in our results.

The trick here is to first sanitize the string and find the opening-paranthesis and remove everything prior to that using REPLACE(). Then, just SUBSTITUTE() the closing-paranthesis. The remainder can be split on spaces using FILTERXML(). To prevent errors and unwanted results I subsituted the ampersand and included accent graves to prevent Excel to recognize these substrings as dates. For more insight on the used xpath and the workings of FILTERXML() I'd like to refer you to this.

edited May 28 '21 at 10:38

answered May 28 '21 at 10:31

JvdV

70,606
8
39
70

Hi JvdV, thanks for this. Unfortunately, it gives me a #VALUE! error. – Robsmith May 28 '21 at 12:10
1

@robsmith, what is your exact input string? Also, are you a 100% sure you have entered this as array-formulae? – JvdV May 28 '21 at 12:13
Hi, JvdV, I entered as an array and tested it on the example I put in the question. – Robsmith May 28 '21 at 12:40
1

@Robsmith, do you have a screenshot of your exact formula and the data? As you can see, it worked on my end just fine – JvdV May 28 '21 at 13:09
Hi JvdV, it's strange. I've added a screenshot. – Robsmith May 28 '21 at 13:15
1

@Robsmith, I'm unfortunately unable to reproduce the error. It works fine on all versions of Excel (including 2016) that I have access to trying this. – JvdV May 28 '21 at 13:38
Thanks for all your help – Robsmith May 28 '21 at 14:29
@Robsmith, any luck yet? – JvdV May 31 '21 at 06:53

Extract Multiple Sub-strings Between Brackets/Parentheses in Excel

1 Answers1