regexp character exclude \r and \n

**klin89** · 04-19-2017, 09:31 PM

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

**MarvinP** · 04-19-2017, 09:40 PM

Hi Klin89 and welcome to the forum.

I expected to see some blanks in your cells but didn't. See if this simple formula works instead of any VBA.

=TRIM(LEFT(A2,FIND("(",A2)-1))

klin89 answer1.xls

**jindon** · 04-19-2017, 10:40 PM

Is this how you wanted?

Sub test()
    Dim r As Range, i As Long
    Dim RegX As Object
    Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = "\([^)]+\)\n*"
        .Global = True
    End With
    With Sheets("Liste")
        For Each r In .Range("a2", .Range("a" & .Rows.Count).End(xlUp))
            r(, 3) = RegX.Replace(r.Value, "")
        Next
    End With
End Sub

**klin89** · 04-20-2017, 03:40 PM

Hello everyone,

Thanks for your reply, however, I had to express myself very badly
The lines are separated by a carriage return in the cell.
I need a macro to keep only the discipline that precedes the parentheses, without leaving space at the end of the string

The picture below shows the desired result

photo1.JPG

klin89

**jindon** · 04-20-2017, 04:39 PM

Then change the pattern in your code to

        .Pattern = "([^\(\n]+)?[ \n]+\(([^\(\)]+)\)"

**MarvinP** · 04-20-2017, 05:14 PM

OK - try this code to do what you want. Run it in the attached...

Sub Klin89()
    Dim LastRow As Double
    Dim RowCtr As Double
    Dim Ary As Variant
    Dim AryCtr As Double
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For RowCtr = 2 To LastRow
    
        Ary = Split(Cells(RowCtr, "A"), Chr(10))
        For AryCtr = 0 To UBound(Ary)
            If InStr(Ary(AryCtr), "(") > 0 Then
                Cells(RowCtr, AryCtr + 3) = Left(Ary(AryCtr), InStr(Ary(AryCtr), "(") - 1)
            Else
                Cells(RowCtr, AryCtr + 3) = Ary(AryCtr)
            End If
        Next AryCtr
    Next RowCtr

End Sub

VBA Split vbNewLine to Rows.xls