Hello forum members,
This is my 1st post, first of all I want to say hello to everyone here
My problem is this:
I would like to extract, from the whole string the string that immediately precedes the parentheses
The cells contain several string separated by a carriage return – linefeed
my data looks something like this - I Need an Output like this
ELMS (United Autosports)
IMSA (Action Express) ------> ELMS IMSA
WRC (Hyundai) ------> WRC
WEC (Toyota)
Formula E (Renault)
Day+Seb (Rebellion) -------> WEC Formula E Day+Seb
NASCAR Cup
(JTG Daugherty) -------> NASCAR Cup
In the attached file, the values are written in column A starting at A2
Very grateful for your help
Option Explicit
Sub test()
Dim r As Range, i As Long
Dim RegX As Object
Set RegX = CreateObject("VBScript.RegExp")
With RegX
.Pattern = "([^\(]+)? ?\(([^\(\)]+)\)"
.Global = True
End With
With Sheets("Liste")
For Each r In .Range("a2", .Range("a" & .Rows.Count).End(xlUp))
If RegX.test(r.Value) Then
For i = 0 To RegX.Execute(r.Value).Count - 1
r(, i + 3).Value = RegX.Execute(r.Value)(i).submatches(0)
Next
End If
Next
End With
End Sub
klin89
Bookmarks