Extract words before brackets based on number of characters within closed brackets

[satish78]

Hi Friends,

I looked some posts over here on how to extract words before closed backets and based on number of characters/letters within closed brackets.
I am looking to do this with formula/vba/udf.
Here is an example

Designed and developed the application using Java Server Faces (JSF) framework and spring web flow	(JSF)	Java Server Faces
Experience in all phases of software development life cycle (SDLC), which includes User Interaction, Business Analysis/Modeling, Design/Architecture, Development, Implementation, Integration, Documentation, Testing, and Deployment	(SDLC)	software development life cycle
Extensive experience in Integrated Development Environment (IDE) such as Eclipse, NetBeans, WSAD, SpringSource tool suite and IntelliJ	(IDE)	Integrated Development Environment
Implemented Hibernate Object-Relational Mapping (ORM) for mapping between the Java classes and Database table.	(ORM)	Object-Relational Mapping
Solid design skills in Java Design Patterns, Unified Modeling Language (UML) and Object Modeling Technique (OMT)	(UML) (OMT)	Unified Modeling Language	Object Modeling Technique

 

[lrobbo314]

How about this?

Book1
	A	B	C	D
1	Designed and developed the application using Java Server Faces (JSF) framework and spring web flow	(JSF)	Java Server Faces
2	Experience in all phases of software development life cycle (SDLC), which includes User Interaction, Business Analysis/Modeling, Design/Architecture, Development, Implementation, Integration, Documentation, Testing, and Deployment	(SDLC)	software development life cycle
3	Extensive experience in Integrated Development Environment (IDE) such as Eclipse, NetBeans, WSAD, SpringSource tool suite and IntelliJ	(IDE)	Integrated Development Environment
4	Implemented Hibernate Object-Relational Mapping (ORM) for mapping between the Java classes and Database table.	(ORM)	Hibernate Object-Relational Mapping
5	Solid design skills in Java Design Patterns, Unified Modeling Language (UML) and Object Modeling Technique (OMT)	(UML) (OMT)	Unified Modeling Language	Object Modeling Technique
Sheet4

Cell Formulas
Range	Formula
B5:D5,B1:C4	B1	=rx(A1)
Dynamic array formulas.

Function RX(s As String)
Dim SP() As String:     SP = Split(s, " ")
Dim CUR As Integer:     CUR = 2
Dim RES() As Variant
Dim POS As Integer

With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "(\(\w+\)),?"
    Set matches = .Execute(s)
    ReDim RES(1 To matches.Count + 1)
    For Each m In matches
        POS = Application.Match(m.Value, SP, 0)
        For i = POS - Len(m.submatches(0)) + 1 To POS - 2
            If i = POS - Len(m.submatches(0)) + 1 Then RES(1) = RES(1) & m.submatches(0) & " "
            RES(CUR) = RES(CUR) & SP(i)
            If i < POS - 1 Then RES(CUR) = RES(CUR) & " "
        Next i
        CUR = CUR + 1
    Next m
End With

RX = RES
End Function

 

[satish78]

How about this?

Book1
	A	B	C	D
1	Designed and developed the application using Java Server Faces (JSF) framework and spring web flow	(JSF)	Java Server Faces
2	Experience in all phases of software development life cycle (SDLC), which includes User Interaction, Business Analysis/Modeling, Design/Architecture, Development, Implementation, Integration, Documentation, Testing, and Deployment	(SDLC)	software development life cycle
3	Extensive experience in Integrated Development Environment (IDE) such as Eclipse, NetBeans, WSAD, SpringSource tool suite and IntelliJ	(IDE)	Integrated Development Environment
4	Implemented Hibernate Object-Relational Mapping (ORM) for mapping between the Java classes and Database table.	(ORM)	Hibernate Object-Relational Mapping
5	Solid design skills in Java Design Patterns, Unified Modeling Language (UML) and Object Modeling Technique (OMT)	(UML) (OMT)	Unified Modeling Language	Object Modeling Technique
Sheet4

Cell Formulas
Range	Formula
B5:D5,B1:C4	B1	=rx(A1)
Dynamic array formulas.

Function RX(s As String)
Dim SP() As String:     SP = Split(s, " ")
Dim CUR As Integer:     CUR = 2
Dim RES() As Variant
Dim POS As Integer

With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "(\(\w+\)),?"
    Set matches = .Execute(s)
    ReDim RES(1 To matches.Count + 1)
    For Each m In matches
        POS = Application.Match(m.Value, SP, 0)
        For i = POS - Len(m.submatches(0)) + 1 To POS - 2
            If i = POS - Len(m.submatches(0)) + 1 Then RES(1) = RES(1) & m.submatches(0) & " "
            RES(CUR) = RES(CUR) & SP(i)
            If i < POS - 1 Then RES(CUR) = RES(CUR) & " "
        Next i
        CUR = CUR + 1
    Next m
End With

RX = RES
End Function

Above VBA Function extracts only words within brackets.
I need full form as well like "Java Server Faces" for JSF in ColumnC
So, in ColumnB (JSF) and in ColumnC "Java Server Faces"

 

[lrobbo314]

What version of Excel are you using?

 

[satish78]

What version of Excel are you using?

pro plus 2019

 

[lrobbo314]

In that case, try this.

Sub RXX()
Dim r As Range:             Set r = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant:        AR = r.Value2
Dim AL As Object:           Set AL = CreateObject("System.Collections.ArrayList")
Dim MX As String
Dim SX As String
Dim SP() As String
Dim POS As Integer

With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "(\(\w+\)),?"
    For i = 1 To UBound(AR)
        SP = Split(AR(i, 1), " ")
        Set matches = .Execute(AR(i, 1))
        For Each m In matches
            MX = MX & m.submatches(0) & " "
            POS = Application.Match(m.Value, SP, 0)
            For j = POS - Len(m.submatches(0)) + 1 To POS - 2
                SX = SX & SP(j)
                If j < POS - 2 Then SX = SX & " "
            Next j
            SX = SX & ";"
        Next m
        AL.Add Trim(MX) & ";" & SX
        MX = vbNullString
        SX = vbNullString
    Next i
End With

With r.Offset(, 1)
    .Value = Application.Transpose(AL.toArray)
    .TextToColumns DataType:=xlDelimited, Semicolon:=True
End With
End Sub

 

[satish78]

In that case, try this.

Sub RXX()
Dim r As Range:             Set r = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant:        AR = r.Value2
Dim AL As Object:           Set AL = CreateObject("System.Collections.ArrayList")
Dim MX As String
Dim SX As String
Dim SP() As String
Dim POS As Integer

With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "(\(\w+\)),?"
    For i = 1 To UBound(AR)
        SP = Split(AR(i, 1), " ")
        Set matches = .Execute(AR(i, 1))
        For Each m In matches
            MX = MX & m.submatches(0) & " "
            POS = Application.Match(m.Value, SP, 0)
            For j = POS - Len(m.submatches(0)) + 1 To POS - 2
                SX = SX & SP(j)
                If j < POS - 2 Then SX = SX & " "
            Next j
            SX = SX & ";"
        Next m
        AL.Add Trim(MX) & ";" & SX
        MX = vbNullString
        SX = vbNullString
    Next i
End With

With r.Offset(, 1)
    .Value = Application.Transpose(AL.toArray)
    .TextToColumns DataType:=xlDelimited, Semicolon:=True
End With
End Sub

getting run time error when I have more than 500 rows

 

[lrobbo314]

I don't think that would have to do with the number of rows. That would mean that it didn't find a match in the string for a text string like (JSF)

Are you sure that line has something like that to match on?

 

[satish78]

I don't think that would have to do with the number of rows. That would mean that it didn't find a match in the string for a text string like (JSF)

Are you sure that line has something like that to match on?

Yes, there is data like JSF

Extract words Containting Capital Letter at begging of word Macro.xlsm
	A
1	is User Acceptance Testing (UAT) is the final stage of any software development life cycle. This is when actual users test the software to see if it is able to carry out the required tasks it was designed to address in real-world situations. UAT tests adherence to customers' requirements.
2	create Technical Specifications for medium size projects and medium risk.
3	Create tickets whenever a Network node goes `down' (not operational).
4	Create weekly technical e-mail newsletter sent to 2,650 clients using HTML.
5	Created a report tracking Access Database along with GUI interfaces to maintain all reporting status viewable using Crystal Enterprise.
6	Created and configured Organizational Structure of the Enterprise which included Organizational Unit, Jobs, Positions using transaction codes PPOCE and PPOME.
7	Created and copied plug-ins to web servers Support other developers in designing and building high quality web applications.
8	Created and Maintained the traceability matrix for the entire project using the Application Management Lifecycle(ALM) Functional and Regression Testing in various environments (Alpha, Beta and production) Active participating in stabilization war rooms before every major release Frequent discussion with the Business analysts and developers when there is a Change Request.
9	Created and modified ancestor data entry, menu and non-visual objects for custom libraries.
10	Created and optimized various T-SQL database objects like tables (schemas), views, stored procedures, User Defined functions (UDF), indexes and triggers using Management Studio and SQL Profiler.
11	Created Assessment OLAP cubes to store historical year to year student information and used to determine Trends.
12	Created new custom reports using Crystal Reports to work with the Allegro system.
13	Created new movement types in the Inventory management system and configured them to work in WM.
14	Created numerous simple to complex queries involving self joins, correlated subqueries and XML techniques for diverse business requirements.
15	Created organizational units, jobs, task and positions assigned the tasks to positions and integrated all of these into the enterprises organizational plan.
16	Created Package Configurations in XML Files to efficiently Promote Unit Tested Packages to Live Environment from Development Environment.
17	Created page objects.
18	Created process for supporting alternative product channels with technical libraries.
19	Created Project Plan for Testing Cycle.
20	Created SAP Users for ESS and defined ESS settings for Time Management, Personal Information.
21	Created test cases, test data and executed them on Window and Mobile based applications.
22	Created test plan and test case documents to be used by test team during system and staging testing Monitored and retested defects to verify that all issues were correctly resolved.
Sheet3

 

[satish78]

I don't think that would have to do with the number of rows. That would mean that it didn't find a match in the string for a text string like (JSF)

Are you sure that line has something like that to match on?

Also getting subscript out of range = run time error 9