CODEWITHSUNDEEP
excelvba
Mojimi
Mojimi

Reputation: 3151

Remove text that is between two specific characters of a string

I have a column with data in the following format:

xxxx(yyyy)

I want to remove what is inside the parentheses, and the parentheses themselves.

Upvotes: 0

Views: 8392

Answers (5)

tfa
tfa

Reputation: 1699

Dim x
x = Split("xxxx(yyyy)", "(")
Dim result
result = x(0)

Upvotes: 0

brettdj
brettdj

Reputation: 55672

You can readily cater for multiple replacements in one string with a Regexp

Sub Test()
Debug.Print CleanStr("xxxx(yyyy)")
Debug.Print CleanStr("and me ()")
Debug.Print CleanStr("and me ()` second string(aaa)")
End Sub

clean string

Function CleanStr(strIn As String) As String
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
   .Pattern = "\([^)]*\)"
   .Global = True
   CleanStr = .Replace(strIn, vbNullString)
End With
End Function

Upvotes: 4

Florent B.
Florent B.

Reputation: 42518

I would use a regular expression for this job:

Sub DeleteMatches()
  Dim cell As Range, re As Object

  ' define the regular expression to match "(...)"  '
  Set re = CreateObject("VBScript.RegExp")
  re.Pattern = "\([^)]*\)"  ' matches "(...)"  '

  ' iterate and clean each cell in range "C2:C100"  '
  For Each cell In Range("C2:C100")
    cell.Value = re.Replace(cell.Value, Empty)
  Next

End Sub

Upvotes: 3

Gary's Student
Gary's Student

Reputation: 96753

Select the cells you wish to process and run this short macro:

Sub DataGrabber()
    Dim r As Range
    For Each r In Intersect(ActiveSheet.UsedRange, Selection)
        If InStr(1, r.Value, "(") > 0 Then
            r.Value = Split(r.Value, "(")(0)
        End If
    Next r
End Sub

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152450

Try this:

=LEFT(A1,FIND("(",A1)-1)

Upvotes: 1

Related Questions