You are here:

Excel/Find text within parentheses set and delete set

Advertisement


Question
QUESTION: Is there a way I can have VB find a bit of text in a cell enclosed with other things within parentheses and delete that out? More or less this:

Column N
Row 1: Blah blah blah (12345 RGT qwerty)
Row 2: Blah blah (23456 RGT poiuy)
Row 3: Blah (Not me)
Row 4: Blah blah blah blah (Not me) (343 RGT lkjklj) (Or me)

I want to remove the parentheses and everything within them when and only when the specific tag of "RGT" occurs. I can't simply use a wildcard because sometimes, as in row 4, replacing (*RGT*) would remove any parenthetical phrases before or after the one that contains "RGT".

Thanks!

ANSWER: Steve,

this worked for me on your test data.  Make a copy of your worksheet.  then select the range to process on the copy of your sheet and run the macro (I assume you want to process multiple cells based on your sample data - if Only one cell, select that cell and a blank cell before running the macro).

Sub abcd()
Dim tgt As String, sAddr As String
Dim schr As String, i As Long, j As Long
Dim iloc As Long, s As String
Dim leftp As Long, rightp As Long
Dim rng As Range
tgt = "RGT"
Set rng = Selection.Find(What:=tgt, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
 sAddr = rng.Address
 Do
   s = ""
   schr = ""
   leftp = 0
   rightp = 0
   iloc = InStr(1, rng.Value, tgt, vbTextCompare)
   For i = iloc To 1 Step -1
     schr = Mid(rng.Value, i, 1)
     If schr = "(" Then
       leftp = i
       Exit For
     End If
   Next
   For j = iloc + Len(tgt) + 1 To Len(rng.Value)
     schr = Mid(rng.Value, j, 1)
     If schr = ")" Then
       rightp = j
       Exit For
     End If
   Next
  If leftp > 0 And rightp > 0 Then
   s = Left(rng.Value, leftp - 1) & " " & _
      Right(rng.Value, Len(rng.Value) - rightp)
   rng = Application.Trim(s)
  End If
  Set rng = Selection.FindNext(rng)
  If rng Is Nothing Then Exit Sub
 Loop While rng.Address <> sAddr
End If
End Sub

Produced:

Blah blah blah
Blah blah
Blah (Not me)
Blah blah blah blah (Not me) (Or me)

from your test data.    

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

Error
Error  
QUESTION: Works great for the most part. But on cells in which there is the tag "RGT" but a missing parenthesis on one side, I get a run-time error (I've attached an image of the error and the code that gets hung up) This happens occasionally in our data, so is there a way to have it select for only those cells in which the RGT is fully enclosed?

Thanks!

Answer
Sub abcd()
Dim tgt As String, sAddr As String
Dim schr As String, i As Long, j As Long
Dim iloc As Long, s As String
Dim leftp As Long, rightp As Long
Dim rng As Range
tgt = "RGT"
Set rng = Selection.Find(What:=tgt, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Do
  sAddr = rng.Address
  s = ""
  schr = ""
  leftp = 0
  rightp = 0
  iloc = InStr(1, rng.Value, tgt, vbTextCompare)
  For i = iloc To 1 Step -1
    schr = Mid(rng.Value, i, 1)
    If schr = "(" Then
      leftp = i
      Exit For
    End If
  Next
  For j = iloc + Len(tgt) + 1 To Len(rng.Value)
    schr = Mid(rng.Value, j, 1)
    If schr = ")" Then
      rightp = j
      Exit For
    End If
  Next
 If leftp > 0 And rightp > 0 Then
  s = Left(rng.Value, leftp - 1) & " " & _
     Right(rng.Value, Len(rng.Value) - rightp)
  rng = Application.Trim(s)
 End If
 On Error Resume Next
 Set rng = Selection.FindNext(rng)
 If Err.Number <> 0 Then
  Err.Clear
  Exit Sub
 End If
 On Error Resume Next
 If rng Is Nothing Then Exit Sub
Loop While rng.Address <> sAddr
End If
End Sub


I couldn't reproduce your error. but have added some code which hopefully will solve the problem.

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.