Macro extracting part of string

[peter_sjogarde]

I am trying to figure out how to keep only a part of a string. The part I want to keep is marked with capitals in the example below.

Example of cell contents:
[something between brackets, like this]something else, belonging to the, same occurrence, PART OF INTEREST;[a new occurrence]with, a string, like this, AND STRING OF INTEREST

So from this cell I want to keep or print in new column "PART OF INTEREST;AND STRING OF INTEREST"

I.e. the part between the last comma and the semicolon for each occurrence and the part after the last comma of the whole string


I do not want a function but rather a macro doing this and puts the result in a new column. I use this code to loop and remove the brackets:

   Dim x As Integer
   ' Set numrows = number of rows of data.
   NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count

   ActiveCell.Select

   For x = 1 To NumRows
      ActiveCell.Offset(1, 0).Select
      Selection.replace What:="
[*]", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
   Next

 

[pgc01]

I'm glad it helped. Thanks for the feedback.

 

[pgc01]

$1

Hi Jon

Another option is to do it without the submatch:

Sub Test()
Dim r As Range, rC As Range
 
Set r = Range("A1:A5")
With CreateObject("VBScript.RegExp")
    .Pattern = "[^;]+?,(?=[^;,]+(;|$))"
    .Global = True
    For Each rC In r
        If .Test(rC) Then rC.Offset(, 1) = .Replace(rC, "")
    Next rC
End With
End Sub