Extract data from right between ()

[NedZep]

I've been trying to pull apart a string into it's parts. I've sorted out some of it, but the last little bit is driving me crazy. Example

CTH KGB01 3.171 (07) (SH) (Scooby Doo)

I need to pull out the Scooby Doo part, and do it for thousands of different lines. The (SH) is not always there, so I can't just use Mid and search for the 3rd (. I need to find from the right, or trim, or something.

Thanks in advance for your expertise!

 

[alansidman]

Please show us a few examples of your data so that we can test appropriately. One record is not enough if the data is not consistent.

 

[CalcSux78]

Not the prettiest formula....

Excel 2010

	A	B
1	CTH KGB01 3.171 (07) (SH) (Scooby Doo)	Scooby Doo

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula B1 {=MID(A1,MAX(IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="(",ROW(INDIRECT("1:"&LEN(A1)))))+1,LEN(A1)-1-MAX(IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="(",ROW(INDIRECT("1:"&LEN(A1))))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[CalcSux78]

NedZep,

If you're comfortable with UserDefinedFunctions & VB stuff... here's a custom function you could use:

Function STRREV(ByVal str As String) As String
Dim n&
n = InStrRev(str, "(") + 1
STRREV = Mid(str, n, Len(str) - n)
End Function

Excel 2010

	A	B
1	CTH KGB01 3.171 (07) (SH) (Scooby Doo)	Scooby Doo

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula B1 =STRREV(A1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Rick Rothstein]

Maybe this formula...

=TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,")","("),"(",REPT(" ",100)),200),100))