Extracting content from cell with multiple parentheses

[Don Iliffe]

I am looking for a formula expression that will let me extract from a cell that contains multiple parentheses to another cell ,specifically in the 2nd instance of parentheses.

The cell content looks like this;
OC (77G201)(A4T201)
OC (ITPN)(GTHS)

As you can see the cell content length can vary.

I can extract from the first instance of parentheses using =mid/find. But I can not figure out how to do a separate extract from the second instance.

Any ideas anyone?

 

[jkpieterse]

Re: Extracting content from cell with multiple paretheses

DO two finds nested in each other and then remove the closing backet:

=SUBSTITUTE(MID(A1,FIND("(",A1,FIND("(",A1)+1)+1,LEN(A1)),")","")

 

[steve the fish]

Re: Extracting content from cell with multiple paretheses

Something like this can help do both:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,")",REPT(" ",99),COLUMNS($A$1:A1)),"(",REPT(" ",99),COLUMNS($A$1:A1)),99,99))

Drag across and down.

 

[Don Iliffe]

Re: Extracting content from cell with multiple paretheses

Thanks for quick response.
Tried that but getting an error, Too many arguments.

 

[Don Iliffe]

Re: Extracting content from cell with multiple paretheses

The =TRIM formula is only giving me the content of the first parentheses instance and not the second instance which desired

 

[steve the fish]

Re: Extracting content from cell with multiple paretheses

The =TRIM formula is only giving me the content of the first parentheses instance and not the second instance which desired

Thats because you didnt follow the drag across and down part.

 

[Don Iliffe]

Re: Extracting content from cell with multiple paretheses

ERR, Whoops!
It is working fine thanks.
Note to self, read the d**n instructions

May thanks,

 

[jtakw]

Re: Extracting content from cell with multiple paretheses

Hi,

Here's another formula that'll work and is more robust against Column(s) insertions.

Formula copied down and across:

Book1
	A	B	C	D
1	OC (77G201)(A4T201)	77G201	A4T201
2	OC (ITPN)(GTHS)	ITPN	GTHS
3	OC (ITPN)(GTHS)(ABCD)	ITPN	GTHS	ABCD
Sheet488

Cell Formulas
Range	Formula
B1	=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,")",""),"(",REPT(" ",99)),COLUMNS($B1:B1)*99,99))

 

[Don Iliffe]

Re: Extracting content from cell with multiple paretheses

Many thanks for that, another for library 'O' stuff

 

[jtakw]

Re: Extracting content from cell with multiple paretheses

You're welcome.