RegEx pattern

Very new to RegEx,
I have in cells in a column like:

Stuff, Other (please specify)

I want

Stuff

I have tried patterns like:

"(, Other\(please specify\))"

or

"(,\sOther\(please specify\))"

Not getting it

Thanks

I am using it in VBA

I am using like this

Sub RemoveRegEx_n()
Dim x
Dim R As Object
Dim ws As Worksheet
Dim rng1 As Range
Dim lngRow As Long

     Set R = CreateObject("vbscript.regexp")
     Set ws = ThisWorkbook.Sheets("XXX")
     With R
       .Global = True
       .Pattern = (,\sOther\(please specify\))
       .ignorecase = True
     End With
     Application.ScreenUpdating = False
     Set rng1 = ws.Range("A1:A" & ws.Cells(Rows.Count, 1).End(xlUp).Row)
     x = rng1.Value2
     For lngRow = 1 To UBound(x, 1)
       x(lngRow, 1) = R.Replace(x(lngRow, 1), vbNullString)
     Next lngRow
     rng1.Value2 = x
     Application.ScreenUpdating = True
End Sub

Apologies about my sloppy question, I have cells like:

Civic Engagement, Community Building, Education, Leadership Development, Other (please specify)

or

Arts, Culture, and Humanities, Community Building, Government and Public Administration, Spirituality/Religion, Other (please specify)

There are more possibilities but all end with

, Other (please specify)

What do you want to return or extract?

From the above

Civic Engagement, Community Building, Education, Leadership Development

Arts, Culture, and Humanities, Community Building, Government and Public Administration, Spirituality/Religion

I want to remove the

, Other (please specify)

from the ends of all the strings in my range

Thanks

Hi..

Rather than using Regex and most probably a loop.. try this..

Private Sub CommandButton21_Click()
    With Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        .Value = Application.Transpose(Split(Replace(Join(Application.Transpose(.Value), vbCrLf), ", Other (please specify)", ""), vbCrLf))
    End With
End Sub

lol.. mm.. yeap.. I may have gone a bit overboard on that one..

Why RegExp?

Just replace ", Other (please specify)" with "" should do....

If you insist

Sub test()
    Dim r As Range
    With CreateObject("VBScript.RegExp")
        .Pattern = "(.+)(?=(, Other \(please specify\)))"
        For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
            If .test(r.Value) Then r.Value = .Execute(r.Value)(0)
        Next
    End With
End Sub

Ah, I just remembered your thread.

http://www.excelforum.com/excel-prog...g-nothing.html

For the learning purpose...

You want to remove

Stuff, Other (please specify)

So the patter should be

"(, Other \(please specify\))"

You missed a space after "Other"...

Originally Posted by capson

I have in cells in a column like:

Stuff, Other (please specify)

I want

Stuff

As I mentioned already, you missed a space after "Other"

You need to replace "Other (please specify)"

Hi capson..

though I admit to being a bit lost on apo's solution

I will try to break it down for you so you might understand what i was thinking..

Note to anyone else: If any of the following (technically or terminology) is incorrect... please let me know..

First.. try stepping through this code.. its' basically the same as the 1 line of code but I have separated it so each line does a specific thing..

IMPORTANT.. make sure your "Locals" window is open.

In the VB Editor.. go to "View" and enable the "Locals" window.

Private Sub CommandButton1_Click()
Dim a, b, c, d, e, f
    With Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        a = .Value
        b = Application.Transpose(.Value)
        c = Join(Application.Transpose(.Value), vbCrLf)
        d = Replace(Join(Application.Transpose(.Value), vbCrLf), ", Other (please specify)", "")
        e = Split(Replace(Join(Application.Transpose(.Value), vbCrLf), ", Other (please specify)", ""), vbCrLf)
        f = Application.Transpose(Split(Replace(Join(Application.Transpose(.Value), vbCrLf), ", Other (please specify)", ""), vbCrLf))
        .Value = Application.Transpose(Split(Replace(Join(Application.Transpose(.Value), vbCrLf), ", Other (please specify)", ""), vbCrLf))
    End With
End Sub

You will see something like this..

Locals.png

Explanation:
a = Shows what the values would look lke when put into a 2 Dimensional array.

b = Transpose the values from the array into a 1 Dimensional array. This is so we can use the Join command next.

c = Join all the elements in the 1 Dimensional array (by the vbcrlf delimiter) into 1 long string.

d = Replace all instances of ", Other (please specify)" with "" in the long string.

e = Use the Split command (using the vbcrlf again as the delimter) to separate the lines into tseparate elements of a 1 Dimensional array.

f = Transpose it back to a 2 Dimensional array so it can written to the sheet correctly.

I hope that helps..

Wow, that helped a lot, I think I actually understood it!!!