Using Mid function

[mrinal saha]

Hi Folks,

I h've few lines below containing the year and some modules in last.
Out of this few are within () and few are outside (). Now I have a formula which gives me imcomplete result. Need your help to complete it. What i want is the year & module, if it is in bracket (year & module) or after bracket or without bracket......


<TABLE style="WIDTH: 204pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=272 border=0><COLGROUP><COL style="WIDTH: 204pt; mso-width-source: userset; mso-width-alt: 9947" width=272><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 204pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: #92d050" width=272 height=17>xxx 2001 (2001 MBIP)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: #92d050" height=17>xxx 2001 (LBO)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: #92d050" height=17>xxxxxzz 2001 (LBO)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: #92d050" height=17>xxx yyyy zzzzzz 2005 SMBIP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: #92d050" height=17>xxx yyyy zzzzzz (2006 MBIP)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: #92d050" height=17>xxx yyyy zzzzzz (lands) 2007 MBIP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: #92d050" height=17>xxx yyyy zzzzzz 2004 MBIP</TD></TR></TBODY></TABLE>

Result using my formula:-
this is the formula -
MID(A26,FIND("(",A26)+1,FIND(")",A26)-FIND("(",A26)-1)
<TABLE style="WIDTH: 122pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=162 border=0><COLGROUP><COL style="WIDTH: 122pt; mso-width-source: userset; mso-width-alt: 5924" width=162><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 122pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=162 height=17>2001 MBIP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>LBO</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>LBO</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>#VALUE!</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>2006 MBIP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>lands</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>#VALUE!</TD></TR></TBODY></TABLE>

thanks,
Mrinal

 

[Robert Mika]

What about this:

=TRIM(SUBSTITUTE(SUBSTITUTE(RIGHT(A1,10),"(",""),")",""))
It works on examples you have provided.

 

[mrinal saha]

Excellent Stuff..many thanks

 

[mrinal saha]

Hey Robert,

Can you please make me understand the formula.

Thanks,
Mrinal