Extracting content from cell with multiple parentheses

Don Iliffe

New Member
Joined
Dec 18, 2008
Messages
15
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

Well-known Member
Joined
Dec 3, 2007
Messages
944
Re: Extracting content from cell with multiple paretheses

DO two finds nested in each other and then remove the closing backet:
Code:
=SUBSTITUTE(MID(A1,FIND("(",A1,FIND("(",A1)+1)+1,LEN(A1)),")","")
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,716
Office Version
365
Platform
Windows
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

New Member
Joined
Dec 18, 2008
Messages
15
Re: Extracting content from cell with multiple paretheses

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

Don Iliffe

New Member
Joined
Dec 18, 2008
Messages
15
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

Well-known Member
Joined
Oct 20, 2009
Messages
7,716
Office Version
365
Platform
Windows
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

New Member
Joined
Dec 18, 2008
Messages
15
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
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:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">OC (77G201)(A4T201)</td><td style=";">77G201</td><td style=";">A4T201</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">OC (ITPN)(GTHS)</td><td style=";">ITPN</td><td style=";">GTHS</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">OC (ITPN)(GTHS)(ABCD)</td><td style=";">ITPN</td><td style=";">GTHS</td><td style=";">ABCD</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet488</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=TRIM(<font color="Blue">MID(<font color="Red">SUBSTITUTE(<font color="Green">SUBSTITUTE(<font color="Purple">$A1,")",""</font>),"(",REPT(<font color="Purple">" ",99</font>)</font>),COLUMNS(<font color="Green">$B1:B1</font>)*99,99</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Don Iliffe

New Member
Joined
Dec 18, 2008
Messages
15
Re: Extracting content from cell with multiple paretheses

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

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Re: Extracting content from cell with multiple paretheses

You're welcome.
 

Forum statistics

Threads
1,077,851
Messages
5,336,759
Members
399,101
Latest member
BharathSanthanam

Some videos you may like

This Week's Hot Topics

Top