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

 

[awoohaw]

could you provide the different scenarios for the "3rd" line? Also, do you have line breaks (ALT-ENTR or char(10)) in the cell?
Does the first line ever have spaces in the text?

 

[TCyber]

could you provide the different scenarios for the "3rd" line? Also, do you have line breaks (ALT-ENTR or char(10)) in the cell?
Does the first line ever have spaces in the text?

No, it will always be a combination of 6 numbers with CCI- at the start of the line


CCI-000015
Support the management of system accounts using (organization-defined automated mechanisms).
NIST SP 800-53 Revision 4::AC-2 (3) or NIST SP 800-53 Revision 4::AC-2 (3) or NIST SP 800-53 Revision 4::MA-1 (5) Etc. pretty much after it says, "Revision 4::" it will be different almost every time and random, there is no pattern I can filter by unless I accomplished this task in a manual sense.
NIST SP 800-53A::AC-2 (1).1
NIST SP 800-53::AC-2 (1)
NIST SP 800-53 Revision 5::AC-2 (1)

 

[awoohaw]

Okay, great on first line. But, you did not address the two other questions I wrote.

1. The forum needs a VERY good representation of what your data looks like. Please post some samples?
2. How do you insert line breaks?

 

[TCyber]

Okay, great on first line. But, you did not address the two other questions I wrote.

1. The forum needs a VERY good representation of what your data looks like. Please post some samples?
2. How do you insert line breaks?

Hey sorry about that I should of highlighted what I was showing. Here is a different example.

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-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-001683 The information system notifies organization-defined personnel or roles for account creation actions. NIST SP 800-53 Revision 4::IA-2 (4) NIST SP 800-53A::AC-2 (4).1 (i and ii) NIST SP 800-53::AC-2 (4)

I am not sure what I can explain for line breaks. The goal is for the data to be formatted easily, with the vital information separated and the other information removed that is not needed. Then, after that is completed, I would use the data sheet to create a pivot table. This would be the ideal situation example below.

CCI-001404

MA-2 (4)

CCI-001405	AC-2 (17)
CCI-001683	IA-2 (4)

 

[awoohaw]

A line break is a conscious decision when typing data into a cell.
After you (or your team) type in the first line what do you (team) do to start a new line in the cell?
(Do you press CNTL-ENTR????)

If you don't I need a key or something that says where the 3rd line begins and ends.

 

[Akuini]

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

 

[awoohaw]

Does the 3rd line always start with NIST, and NIST is never in the 1st or 2nd?

 

[TCyber]

Does the 3rd line always start with NIST, and NIST is never in the 1st or 2nd?

It is always in the 3rd line and no the second or first line would not be different

 

[Akuini]

@TCyber
Did you try my code in post #7?