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?
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

jkpieterse

Well-known Member
Joined
Dec 3, 2007
Messages
1,006
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,908
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,908
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,979
Messages
5,411,636
Members
403,383
Latest member
Excelacity

This Week's Hot Topics

Top