VBA from text to number

[SamT]

Option Explicit

Sub Rename_Dept_Fund_Sheets()
'This will skip any Sheet with an A6 String that does not have exactly 2 complete sets of parentheses
'Example: Fund: UNRESTRICTED FUND 1), Department: HALL OF FAME DINNER (720)
'Example: Fund: UNRESTRICTED FUND 1, Department: HALL OF FAME DINNER (720)
'Example: Fund: UNRESTRICTED FUND(1), Department(#3): HALL OF FAME DINNER (720)

Dim FundNum As String
Dim DeptNum As String
Dim X As Variant
Dim Sht As Worksheet

For Each Sht In ActiveWorkbook.Sheets
   If Not InStr(Sht.Range("A6"), ")") = 2 Then GoTo NextSht 'Note the last close Paren is part of InStr()
   If Not InStr(Sht.Range("A6"), "(") = 2 Then GoTo NextSht 
   On Error GoTo NextSht

    X = Split(Sht.Range("A6"), "(") 'pay attention to the structure of Split. The last Close Paren is part of Split()
   FundNum = Split(X(1), ")")(0) 'X(1) is the second element of X, (0) is the first element of Split
   DeptNum = Split(X(2), ")")(0) 'X(2) is the third element of X, (0) is the first element of Split
   Sht.Name = DeptNum & "_" & FundNum
NextSht:
Next Sht

End Sub

I recommend you place this code in a module in the "Personal" Workbook and run it from the Macro menu in the workbook you want to rename the sheets in.