Extracting text from cell

[nn992]

Hello guys,

I have large xls file wherein one cell has 100+ words...
Out of that specific cell (J1,J2,j3...) I want to extract content which is in "[ ]" square brackets. The problem is that there is sometimes one pair of brackets in the cells, but sometimes is also 3-4 pairs of brackets...


I managed to extract from first bracket pair, but for other ones, I cant do it.

Hope someone can help. If you need more information, comment please

Thanks in advance

 

[wideboydixon]

Using a formula:

Book1
	J	K	L	M	N	O
1	[a]bc[d]ef[g]hi[j][k]	a	d	g	j	k
Sheet1

Cell Formulas
Range	Formula
K1	=IFERROR(MID($J1,FIND(CHAR(1),SUBSTITUTE($J1,"[",CHAR(1),COLUMNS($K1:K1)))+1,FIND(CHAR(1),SUBSTITUTE($J1,"]",CHAR(1),COLUMNS($K1:K1)))-FIND(CHAR(1),SUBSTITUTE($J1,"[",CHAR(1),COLUMNS($K1:K1)))-1),"")

Copy formula in K1 across and down as necessary.

WBD

 

[FormR]

Hi, and just another option..:

Excel 2013/2016
	J	K	L	M
1	text [first part] text text text [second] text text [third one]	first part	second	third one
2	text [first part] text text text text text [second one]	first part	second one
3	text [just one] text text text text text	just one
Sheet1

Cell Formulas
Range	Formula
K1	=IFERROR(TRIM(LEFT(SUBSTITUTE(MID($J1,FIND("#",SUBSTITUTE($J1,"[","#",COLUMNS($K1:K1)))+1,255),"]",REPT(" ",255)),255)),"")

 

[nn992]

Hi guys,

Thanks a lot for the fast ansvers, but unfortunately it doesn't work for me../

it asks me to correct the formula, and instead of '','' to put '';'' , which after correction returns wrong solution (nothing appears) :/

 

[FormR]

it asks me to correct the formula, and instead of '','' to put '';'' , which after correction returns wrong solution (nothing appears) :/

Can you post the amended formula that you tried?

EDIT: And can you post a few examples of your data in your reply.

 

[taurean]

Hi guys,

Thanks a lot for the fast ansvers, but unfortunately it doesn't work for me../

it asks me to correct the formula, and instead of '','' to put '';'' , which after correction returns wrong solution (nothing appears) :/

It seems to be specific to your locale where argument separator for formula is ";". So if you replace all "," with ";" then FormR formula should look like:
=IFERROR(TRIM(LEFT(SUBSTITUTE(MID($J1;FIND("#";SUBSTITUTE($J1;"[";"#";COLUMNS($K1:K1)))+1;255);"]";REPT(" ";255));255));"")

 

[nn992]

Works now... thanks a lot!