0

I would like to ask for your help, with the below. So far, I only found a solution that extracts the first required string, but ignores the rest of them in a cell.

Starting data (each row represents separate cell in a column):

This is a [table].
[Table] has four [legs] and a board.
On the table, you can put a [plate], [fork] and [knife]
[Anything]
Version of the table is [2.0].
There can by [any val_ues12] in brackets

I need to extract all the values in the square brackets to separate cells and include the square brackets. For the above example, it would be great if there was a second sheet created with following cells:

[table]
[Table]
[legs]
[plate]
[fork]
[knife]
[Anything]
[2.0]
[any val_ues12]

A link to sample Excel containing formula I currently have:

Excel to download

See the screenshot of sample Excel:

sample Excel

Can anyone help with this, please?

  • (1) Your example is unfortunate, because Excel has things called “tables”, and for a moment I thought that’s what you were talking about.  (2) Please show us what you have so far (and where you found it). (3) Can we set a limit on the number of words in a cell; e.g., a cell will have no more than four [bracketed] words? ten? (4) Are you looking only for a VBA solution, or will you accept a worksheet formula solution? – G-Man Nov 15 '17 at 6:28
  • Hi G-Man and thank you for your reply. I will try to address all the points mentioned by you: 1 - not sure what you mean here, talking about tables, indeed 2 - Added a sample Excel with formula I have at the moment as an attachment to the original post 3 - number of expressions in square brackets differ, if it helps, than 7 should be OK (please note that anything can be in square brackest, from single character, to several words/numbers) 4 - any solution that will work Thank you again for your help – Jan Nieboras Nov 15 '17 at 7:31
0

I would like to suggest solution for the query, where you are trying to extract text within Box Brackets. In fact I'm posting two Formula. The first will extract only the 1st occurrence but the second will pick all the occurrences.

enter image description here

First Formula:

=IFERROR(MID(E290,FIND("[",E290)+1,FIND("]",E290)-FIND("[",E290)-1),"")

Second Formual:

=TRIM(MID(REPT(" ", 999)&SUBSTITUTE(SUBSTITUTE($E290, "[", REPT(" ", 999)), "]", REPT(" ", 999)), COLUMN(A:A)*1999, 999))

NB: This screenshot is for Formula 2, Data Range is E290 to E293. Formula is in F290. You can write the 1st formula in I290. Drag the 2nd Formula Right then Down.

Change the data range as your need.

Hope this help you, please remember I've posted the solution after formula has been tested by me.

  • thank you very much for your help. There are two things I would like to ask for, if possible: 1. Words should be extracted including the square brackets (this can be done after extraction, I assume with another formula, but if it could be in one formula only, that would be great). 2. For the second formula, I was not able to copy it successfully to my Excel. Maybe you could send me your working file, so I can have a look? Thanks a lot anyway – Jan Nieboras Nov 17 '17 at 7:31
0

@Jan as you have demanded, now I would like to suggest you a different Formula which extracts the Text with Box Brackets.

enter image description here

Formula is, =MID(E306,SEARCH("[",E306),SEARCH("]",E306)-SEARCH("[",E306)+1)

NB: My Data Range is E306 to E308 and the Formula is in F306.

Regarding the Second Formula I've had posted Yesterday if you are unable to copy then simply write it down on a paper and finally insert into the required Cell (don't forget to Drag it 2 or 3 Cells Right and Down till the Data Range).

Hope the New Formula help you.

  • Hi @Rajesh S, thanks a lot again. Would it be possible for you to update the second formula so it includes the square brackets as well? I've managed to add them to the beginning and the end, but this means having [] in the cells that should be empty: ="["&TRIM(MID(REPT(" ", 999)&SUBSTITUTE(SUBSTITUTE($E290, "[", REPT(" ", 999)), "]", REPT(" ", 999)), COLUMN(A:A)*1999, 999))&"]" – Jan Nieboras Nov 17 '17 at 9:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.