Sorting by values in parenthesis witihin a field

[luxvivens]

My data source is an Excel spreadsheet. I have measure names that are in a cell that also has the name of the Department in parenthesis.

Example:
% Cancellations Day of Surgery-PSL (Main OR)
% Cancellations Day of Surgery-PSL (Perioperative Services)
% Change in Overall Pain Score Intensity - Pain Clinic (Ambulatory Care Center)

Is there a way to extract the Department name contained within the parenthesis so that I could sort ascending by Department Name so measures are displayed in Department Name ascending order (e.g. Ambulatory Care, Main OR and then Perioperative Services)?

I have about 300 different measure names and approximately 40 different Department Names.

I am using CR10.

 

[fisheromacse]

if there is always only 1 pair of parenthesis, something like this might work for you. It may need tweaked or i may have missed something and gone down a complete rabbit trail, but here goes:

//MID
numbervar p1 := instr({table.field},"(")l
numbervar p2 := instr([table.field},")");

MID({table.field},p1,(p2-p1))

 

[luxvivens]

Totally awesome! It works. I am just trying to get rid of the the first parenthesis in the output.

For example, I get the following:

(Pharmacy

But I can work around that.

Thanks so much!

 

[fisheromacse]

i think it will start after the parenthesis if you change the last line of the formula to:

MID({table.field},p1+1,(p2-p1))

 

[luxvivens]

Yep that did it. Also added a -1 st the end.
Thanks so much.