Extract data between parenthesis in calculated value field

Question

I'm using SharePoint 2007 and I would like to extract data from parenthesis from another column to a calculated field in the same list.

For example: Column named "Team", value "RED (Mark, John, Lisa)". The calculated value should be "Mark, John, Lisa".

I've tried this formula:

=TRIM(LEFT(RIGHT(SUBSTITUTE("("&SUBSTITUTE([TEAM],")","("),"(",REPT(" ",99)),198),99))

But I've read that SUBSTITUTE function is not available in SharePoint.

Also I would like to return a specified string (like "PAY ATTENTION! NO PARENTHESIS!") if there are no parenthesis.

Is that possible? Any suggestions?

Thanks!

Answer 1

I might be mis-understanding your question but it seems that a simple =MID() function would work. Something like

=MID([TEAM], SEARCH("(",[TEAM],0)), SEARCH(")", [TEAM], 0))

To add your return message, just encase all of it in an IF statement.

Also, if this isn't sufficient, please edit your question to explain.