Formula help please - Find & Left?

Allan Walker

New Member
Joined
Jun 10, 2010
Messages
49
Hi,

I am trying to extract any string inside an open bracket ( and closed bracket ) in a cell. The cell data can contain a string prefixing the open bracket, but thankfully, there is no string suffixing the closed bracket.

The string lengths inside and outside the brackets are variable in the dataset, otherwise I would have been able to do a "RIGHT" or "LEFT" or "MID", so now I'm stuck!

Prior

Excel Workbook
GHIJ
1MARY COOK(PR271)*YASMIN AKHTAR (YA1)*
2BETTY YATES(WD305)*YASMIN AKHTAR (YA1)*
3JOAN OWEN(PR304)*YASMIN AKHTAR (YA1)*
4AVTAR SINGH(WD401)*YASMIN AKHTAR (YA1)*
5HILDA *YOUNG(WD405)*YASMIN AKHTAR (YA1)*
6PERCY COLEMAN(WD410)*YASMIN AKHTAR (YA1)*
7AUGUSTA BELL(WD273)*YASMIN AKHTAR (YA1)*
Sheet 1


Result

Excel Workbook
GHIJ
1MARY COOK(PR271)(PR271)YASMIN AKHTAR (YA1)(YA1)
Sheet 1


I am using Office 2003 with Windows XP SP3

Many thanks in advance,

Allan
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">werbgwr(werg)</td><td style="text-align: right;;"></td><td style=";">(werg)</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">wbtqvq4q3(vew)</td><td style="text-align: right;;"></td><td style=";">(vew)</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">wevrwerverw(wvebr)Wrbtw</td><td style="text-align: right;;"></td><td style=";">(wvebr)</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><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: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><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: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=MID(<font color="Blue">A1,FIND(<font color="Red">"(",A1</font>),(<font color="Red">FIND(<font color="Green">")",A1</font>)</font>)-FIND(<font color="Red">"(",A1</font>)+1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=MID(<font color="Blue">A2,FIND(<font color="Red">"(",A2</font>),(<font color="Red">FIND(<font color="Green">")",A2</font>)</font>)-FIND(<font color="Red">"(",A2</font>)+1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=MID(<font color="Blue">A3,FIND(<font color="Red">"(",A3</font>),(<font color="Red">FIND(<font color="Green">")",A3</font>)</font>)-FIND(<font color="Red">"(",A3</font>)+1</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:
Upvote 0
Excel Workbook
GHIJ
1MARY COOK (PR271)(PR271)YASMIN AKHTAR (YA1)(YA1)
2BETTY YATES (WD305)(WD305)YASMIN AKHTAR (YA1)(YA1)
3JOAN OWEN (PR304)(PR304)YASMIN AKHTAR (YA1)(YA1)
Sheet1
Excel 2007
Cell Formulas
RangeFormula
H1=MID(G1,FIND("(",G1),12)
J1=MID(I1,FIND("(",I1),12)
 
Upvote 0

Forum statistics

Threads
1,224,514
Messages
6,179,219
Members
452,895
Latest member
BILLING GUY

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top