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!