Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sorting by values in parenthesis witihin a field

Status
Not open for further replies.

luxvivens

Technical User
May 20, 2010
27
CA
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.
 
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))
 
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!
 
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))
 
[bigears]Yep that did it. Also added a -1 st the end.
Thanks so much.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top