Mod in Bracket ()

[kelvin0403]

<TABLE style="WIDTH: 397pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=529><COLGROUP><COL style="WIDTH: 345pt; mso-width-source: userset; mso-width-alt: 16822" width=460><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 345pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl68 height=21 width=460>Tester INFO

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 52pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl68 width=69>Test Time</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=21>TES22H4(0.25),TES22H3(0.55),TES22H2(0.49),TES22H1(0.51)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #3867a6 1pt solid" class=xl69 align=right>0.55</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>TES22H4(0.25),TES03H1(0.25),TES22H2(0.35)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 align=right>0.35</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>TES03H1(0.26)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 align=right>0.26</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>TES03H1(1),TES04H1(1),TES04H2(1.2)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 align=right>1.2</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>TES03H1(2)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 align=right>2</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>TES03H1(1)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 align=right>1</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>QTT06H1(0.381),QTT05H1(0.381),QTT04H1(0.681)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 align=right>0.681</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21>QTT06H1(0.383),QTT05H1(0.381),QTT04H1(0.389)</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl66 align=right>0.389

</TD></TR></TBODY></TABLE>

Hi, I would like to have a table like the above.
The tester time should show the data with highest value in the bracket ().
Pls help what formula I should use?

 

[kelvin0403]

Hello,

Anyone have idea bout this?

 

[Weaver]

I think maybe you'd need to have a series of helper columns to get the individual values out. What's the most numbers in brackets you'd have in one cell?

 

[kelvin0403]

There are no fix tester info in the same cell. Some cell contain more than 10 testers info. The only thing I need to get is the highest test time which appear in ().

 

[Weaver]

Here's a user defined function.

Hold alt & hit F11, you should get the vb window

Choose insert -> module

Paste the following code

Function bracketMax(ByVal str As String) As Double
    If InStr(str, "(") > 0 Then
        s = Split(str, "(")
        For i = 0 To UBound(s)
            t = Val(Replace(s(i), ")", ""))
            If t > bracketMax Then bracketMax = t
        Next i
    End If
End Function

In B2, use

=bracketmax(A2)

and copy down

If there's a way of doing this without macros or helper columns, I'd like to see it.

 

[Jon von der Heyden]

The best I can do, using native formula, is as follows:
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Tester INFO</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style=";">Test Time</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">TES22H4(0.25),TES22H3(0.55),TES22H2(0.49),TES22H1(0.51)</td><td style="text-align: right;;">0.55</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">TES22H4(0.25),TES03H1(0.25),TES22H2(0.35)</td><td style="text-align: right;;">0.35</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">TES03H1(0.26)</td><td style="text-align: right;;">0.26</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">TES03H1(1),TES04H1(1),TES04H2(1.2)</td><td style="text-align: right;;">1.2</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">TES03H1(2)</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">TES03H1(1)</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">QTT06H1(0.381),QTT05H1(0.381),QTT04H1(0.681)</td><td style="text-align: right;;">0.681</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">QTT06H1(0.383),QTT05H1(0.381),QTT04H1(0.389)</td><td style="text-align: right;;">0.389</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 #BBB;border-top:none;text-align: center;background-color: #DAE7F5;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: #BBB"><thead><tr style=" background-color: #DAE7F5;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: #DAE7F5;color: #161120">B3</th><td style="text-align:left">=MAX(<font color="Blue">IF(<font color="Red">ISNUMBER(<font color="Green">--TRIM(<font color="Purple">MID(<font color="Teal">SUBSTITUTE(<font color="#FF00FF">SUBSTITUTE(<font color="Navy">A3,"(",REPT(<font color="Blue">" ",255</font>)</font>),")",REPT(<font color="Navy">" ",255</font>)</font>),255*{1,3,5,7,9},255</font>)</font>)</font>),--TRIM(<font color="Green">MID(<font color="Purple">SUBSTITUTE(<font color="Teal">SUBSTITUTE(<font color="#FF00FF">A3,"(",REPT(<font color="Navy">" ",255</font>)</font>),")",REPT(<font color="#FF00FF">" ",255</font>)</font>),255*{1,3,5,7,9},255</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[kelvin0403]

Thanks Jon von der Heyden******** type=text/javascript> vbmenu_register("postmenu_2810497", true); *********>, this formula works perfectly! You are just like God!