# How to extract multiple data in a cells to separate cells

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:

See the screenshot of 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

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.

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

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

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).