I would think a formula might be neat, but as I'm more 'formula challenged' than 'vba challenged', here's a shot:
Option Explicit
Sub RetCounts()
Dim REX As Object,
Dim rngData As Range,
Dim aryIn As Variant,
Dim aryOut As Variant,
Dim i As Long
'// Change sheetname and range to suit//
With ThisWorkbook.Worksheets("Sheet1")
Set rngData = Range(.Range("A2"), .Cells(Rows.Count, 1).End(xlUp))
End With
aryIn = rngData.Value
ReDim aryOut(1 To UBound(aryIn, 1), 1 To 1)
Set REX = CreateObject("VBScript.RegExp")
With REX
.Global = True
.Pattern = ".*?\(|\).*"
For i = LBound(aryIn, 1) To UBound(aryIn, 1)
If .Test(aryIn(i, 1)) Then
aryOut(i, 1) = Len(Trim(.Replace(aryIn(i, 1), vbNullString)))
Else
aryOut(i, 1) = 0
End If
Next
End With
rngData.Offset(, 1).Value = aryOut
End Sub
Mark