Only keeping certain text in a cell

[TCyber]

I have a very large worksheet full of data that I am moving over to a pivot table to display the information the way it is most useful. My challenge is that one of the columns provides way more information than needed, but what is in the cell (over 500 in the column) is vital. What I would like to do is keep the first line and if possible, take NIST SP 800-53 Revision 4: AC-2 (1) out of it. Where I struggle is that the first and third lines will not always be the same CCI or AC-2 could be several other combinations. I really need to be able to capture the data that is lines 1 and 3. It is even better if I can, all in one swoop, move the output of line three into another cell to the right of it. However, it is possible I can do that in another step.

Any pointers or guidance is greatly appreciated

CCI-000015 Support the management of system accounts using (organization-defined automated mechanisms). NIST SP 800-53 Revision 4::AC-2 (1) NIST SP 800-53A::AC-2 (1).1 NIST SP 800-53::AC-2 (1) NIST SP 800-53 Revision 5::AC-2 (1)

CCI-000015 - Keep this NIST SP 800-53 Revision 4::AC-2 (1) - Keep this and move to another blank column

 

[TCyber]

Maybe this:

Sub TCyber_1()
Dim i As Long
Dim va, vb, ary, arx
va = Range("A2", Cells(Rows.Count, "A").End(xlUp)) 'data start at A2
ReDim vb(1 To UBound(va, 1), 1 To 2)
For i = 1 To UBound(va, 1)
    If va(i, 1) <> "" Then
    ary = Split(va(i, 1), vbLf)
        vb(i, 1) = ary(0)
        If UBound(ary) > 1 Then
            arx = Split(ary(2), "::")
            vb(i, 2) = arx(UBound(arx))
        End If
    End If
Next
'put the result at B2 downward
Range("B2").Resize(UBound(vb, 1), 2) = vb
End Sub

Example:

Book1
	A	B	C
1
2	CCI-001404 Automatically audit account disabling actions. NIST SP 800-53 Revision 4::MA-2 (4) NIST SP 800-53 Revision 4::AC-2 () NIST SP 800-53A::AC-2 (4).1 (i and ii) NIST SP 800-53::AC-2 (4)	CCI-001404	MA-2 (4)
3	CCI-001405 Automatically audit account removal actions. NIST SP 800-53 Revision 4::AC-2 (17) NIST SP 800-53A::AC-2 (4).1 (i and ii) NIST SP 800-53::AC-2 (4) NIST SP 800-53 Revision 5::AC-2 (4)	CCI-001405	AC-2 (17)
Sheet3

Wow, that was awesome thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[Cubist]

Formula option

Book1
	A	B	C
2	CCI-001404 Automatically audit account disabling actions. NIST SP 800-53 Revision 4::MA-2 (4) NIST SP 800-53 Revision 4::AC-2 () NIST SP 800-53A::AC-2 (4).1 (i and ii) NIST SP 800-53::AC-2 (4)	CCI-001404	MA-2 (4)
3	CCI-001405 Automatically audit account removal actions. NIST SP 800-53 Revision 4::AC-2 (17) NIST SP 800-53A::AC-2 (4).1 (i and ii) NIST SP 800-53::AC-2 (4) NIST SP 800-53 Revision 5::AC-2 (4)	CCI-001405	AC-2 (17)
Sheet3

Cell Formulas
Range	Formula
B2:C3	B2	=LET(ts,TEXTSPLIT(A2,,CHAR(10)),TEXTSPLIT(TEXTJOIN("|",,INDEX(ts,1),TEXTAFTER(INDEX(ts,3),":",-1)),"|"))
Dynamic array formulas.