Search for parentheses without vba thinking it's the end of the formula

[chrfra]

Hi. I need to truncate all the text after an end parentheses but, not surprisingly, vba thinks the parentheses is the end of my argument. How do I tell vba that it's the text I'm looking for instead of seeing it as the end of the argument? Here is the code:
A1 = Left(A1, Find(")", A1) + Len(") ") - 1)

I'm trying to get rid of the text after a student's name so it will be usable in a pivot table later. (See attached.) I need everything after the grade truncated, which I know how to do if I can only get the ) recognized as text.

This is the last piece I need and it's driving me nuts! Thanks!

 

[Fluff]

Hi & welcome to MrExcel.
If you want to get rid of everything after the students name why are you looking for ) rather than(
Also if you want help with VBA, it would help if you posted your code, rather than a formula.

 

[chrfra]

I want his name and grade. I need the extra spaces and the "Count: 4" gone.

Basically, I have about 250 students that have missing assignments. Due to privacy concerns, each kid has a separate tab. We had to get the data from a school report that only makes pdf's. So, I convert it from the pdf to the excel sheet. Then I have some stuff I need to do to make it usable for the teachers and the counselors. It exports weirdly. For 90% of it, it exports the names, including the grade level as part of the name, separately from the count of missing assignments. However, for whatever reason, 10% are like the example I showed in the example - the name, grade and count are combined into one, long cell. If the cell is combined, the code deletes everything on the page. If I manually truncate the name, the code works as desired. I thought it would be quick and easy to automatically truncate it but, hence the question to this forum. Further, the counselors have their own programs and need a cleaned up name/grade level later on, so it needs to be cleaned up for that reason as well. We don't use the count for anything so it can go away. Here is the full code (note, it's been YEARS since I coded so it may not be the most efficient...happy to take any advice at all):

Sub Arg()
'
' Arg Macro
'

' Set variables
    Dim WS_Count As Integer
    Dim I As Integer
    Dim StudentName As String

    ' Set WS_Count equal to the number of worksheets in the active
    ' workbook.
    WS_Count = ActiveWorkbook.Worksheets.Count

    ' Begin the loop.
    For I = 1 To WS_Count
              
   ' For Each ws In ActiveWindow.SelectedSheets
    
        Worksheets(I).Activate
     '           StudentName = Range("A2").Value
      '          MsgBox StudentName
       
   ' Delete first row
  
        Rows("1:1").Select
        Selection.Delete Shift:=xlUp
        StudentName = Range("A1").Value
        MsgBox StudentName
       
        If Range("A1").Value Like "Count" Then
            MsgBox "Has it"
           A1 = Left(A1, Find(")", A1) + Len(") ") - 1)
         Else
           A1 = A1
        End If

   ' Delete Points Column
        If Range("D2").Value Like "*Possible*" Then
             Columns("D:D").Select
             Selection.Delete Shift:=xlToLeft
         End If
   ' Delete Flag Column
        If Range("E2").Value Like "*Flag*" Then
            Columns("E:E").Select
            Selection.Delete Shift:=xlToLeft
        End If

   ' Sort fields but not if formatting is wrong. Check for format issue first.
        If IsEmpty(Range("F2:I2").Value) = True Then
            MsgBox I
             Range("A2:D251").Select
             ActiveWorkbook.Worksheets(I).Sort.SortFields.Clear
             ActiveWorkbook.Worksheets(I).Sort.SortFields.Add2 Key:=Range("A3:A250" _
                 ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
             ActiveWorkbook.Worksheets(I).Sort.SortFields.Add2 Key:=Range("C3:C250" _
                 ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                 With ActiveWorkbook.Worksheets(I).Sort
                     .SetRange Range("A2:D12")
                     .Header = xlYes
                     .MatchCase = False
                     .Orientation = xlTopToBottom
                     .SortMethod = xlPinYin
                     .Apply
                 End With
            Else
                Range("A1").Select
        End If
        Range("A1").Select
            
    Next I
   
End Sub

 

[Fluff]

How about

If Range("A1").Value Like "*Count*" Then
   MsgBox "Has it"
   Range("A1").Value = Left(Range("A1"), InStr(1, Range("A1"), ")") - 1)
End If

 

[chrfra]

That works! It takes off the end parentheses but that's just a minor formatting thing. The counselors can deal.

Thank you!!

 

[Fluff]

To keep the bracket, just get rid of the -1 at the end.

 

[chrfra]

Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.