Macro to extract string withing the parentheses

[zabiullakhan]

I have range of cells which have the data in the below format

Bangalore (India); Manila (Philippines)
Bangalore (India);
USA
USA; India

When i run the macro on the above string need to extract the text within the parentheses
OutPut as below

India; Philippines
India;
USA 
USA; India

Please help

 

[djreiswig]

Are the first and the fourth rows of your data one column or two? Meaning can there be more than one set of parenthesis in a cell?

 

[zabiullakhan]

Yes the first 4 rows data is in one collumn itself and the output data should also go to only one collumn.

 

[Rick Rothstein]

How about a UDF (user defined function)? Put this code is the same module that you would put a macro in...

Function InParens(S As String) As String
  Dim X As Long, Parts() As String
  If Len(S) = 0 Then Exit Function
  If InStr(S, ")") Then
    Parts = Split(S, ")")
    For X = 0 To UBound(Parts) - 1
      InParens = InParens & "; " & Split(Parts(X), "(")(1)
    Next
    InParens = Mid(InParens, 3)
  Else
    InParens = S
  End If
End Function

Now, go back to your worksheet. Assuming text is in the range A1:A4, put this formula in B1 and copy it down to B4 (or beyond if you want it in placed for future entries to Column A)...

=InParens(A1)

 

[Sektor]

Function RemPar(Str As String) As String

    Dim re As Object
    
    Application.Volatile

    Set re = CreateObject("VBScript.RegExp")

    With re
        .Global = True
        .Pattern = "\s+\(\w+\)"
    End With
    
    RemPar = re.Replace(Str, vbNullString)

End Function