Extracting text from a second set of brackets from a cell to the left.

[lemonwater]

I have the formula =TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(B2, ")",""),"(",REPT(" ",99)),99))
which aims extract text from a second set of brackets in a cell, however my brackets in the cell are [ ] not (). What do I need to change about the above to make this work for my brackets please

[AliGW]

Welcome to the forum.

Assuming you mean that BOTH sets of brackets are square ones:

=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(B2, "]",""),"[",REPT(" ",99)),99))

If not, then there are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

[Glenn Kennedy]

FILTERXML is also a greta way of parsing complex strings:


=FILTERXML("<A><B>"&SUBSTITUTE(SUBSTITUTE(B2,"[","</B><B>",2),"]","</B><B>",2)&"</B></A>","//B[2]")

again, assiuming both sets of parentheses are square

[lemonwater]

Thank you so much for the welcome and your expertise.
The formula worked very well, however, it is also extracting the text that follows the second sett of bracketed text. For example the result can look like this:
CIRET [3pc] PREMIER SYNTHETIC BRUSH SET [15060310] 1x25,1x38,1x50mm (result) 15060310 1x25,1x38,1x50mm
How do I alter the formula to stop it extracting text following the desired extract.
Any further help would be greatly appreciated.

Regards
Angus

[Glenn Kennedy]

As I said... FILTERXML is very powerful. It works on your data.

[lemonwater]

Thank you very much Glenn. This also works well. Your help is very much appreciated.
Angus

[AliGW]

The formula worked very well, however, it is also extracting the text that follows the second sett of bracketed text.

Next time provide some sample data (15-20 rows of it) with worked examples of what you want.

I answered your question based on the information you gave in the opening post. As you didn't disclose anything more, I did not anticipate there being anything more to take into consideration.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[Glenn Kennedy]

I'm not sure about the "as well" bit... as it did NOT return the subsequent, unwanted data... but whatever!

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.