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
CTH KGB01 3.171 (07) (SH) (Scooby Doo)	Scooby Doo

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]{=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))))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[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
CTH KGB01 3.171 (07) (SH) (Scooby Doo)	Scooby Doo

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=STRREV(A1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Maybe this formula...

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