export multiple doc numbers and dates from same cell

[algere]

I am trying to search one column for two keywords: "CO" and "LOTD". I would like to export string with keywords starting with keyword "CO" or "LOTD" through date. i.e.

Start:

EA: Turned on per SAL from XXX to XX LOTD T020 dtd 23 FEB 10. Turned on per SAL from Fluor to DI LOTD T069 dtd 07 JUL 10.

<tbody>
</tbody>

End:
LOTD T020 dtd 23 FEB 10, LOTD T069 dtd 07 JUL 10


Please help!

 

[Matt Rogers]

Hi.

<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='3' style='font-family:Calibri; color:#000000; background-color:#FFFFFF; font-size:11px; font-weight:normal; font-style:normal; '><colgroup><col width='28pt'><col width='354,75pt'><col width='267pt'></colgroup><tr style='background-color:#cacaca'><td> </td><td align='middle'>A</td><td align='middle'>B</td></tr><tr><td style='background-color:#cacaca' align='middle'>1</td><td align='left' >Turned on per SAL from XXX to XX LOTD T020 dtd 23 FEB 10.</td><td align='left' >LOTD T020 dtd 23 FEB 10.</td></tr><tr><td style='background-color:#cacaca' align='middle'>2</td><td align='left' >Turned on per SAL from Fluor to DI LOTD T069 dtd 07 JUL 10.</td><td align='left' >LOTD T069 dtd 07 JUL 10.</td></tr></table><br><table border='3' cellspacing='0' cellpadding='2' valign='middle' colspan='2' style='table-layout:auto; color:#000000; background-color:#FFFFFF; font-family:Calibri; font-size:10px;'><colgroup><col width='40pt'><col></colgroup><tr style='background-color:#eeaaaa'><td>Cell</td><td>Formula</td></tr><tr><td>B1</td><td><Span style='color:#222222'>=IF</Span><Span style='color:#0000DD'>(COUNTIF</Span><Span style='color:#222222'>(A1,"*LOTD*")</Span><Span style='color:#0000DD'>=1,RIGHT</Span><Span style='color:#222222'>(A1,25)</Span><Span style='color:#0000DD'>,"")</Span><Span style='color:#222222'></Span></td></tr></table>

 

[algere]

Thanks Matt but doing it that way I would have to do it three times, once for Left, once for MID and once for Right... then I would have to do it again for "CO". So it would be six columns and I'd have to combine all of them into one cell - making a seventh column.

 

[JLGWhiz]

See if this will work. This assume data is in column A of source sheet, and creates new sheet as destination.

Sub wildhair()
Dim sh As Worksheet, newSh As Worksheet, lr As Long, rng As Range, exp As Variant
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A2:A" & lr)
Set newSh = Sheets.Add(After:=Sheets(Sheets.Count))
    For Each c In rng
        If InStr(c.Value, "CO") > 0 Or InStr(c.Value, "LOTD") > 0 Then
            exp = Split(c.Value, ".")
            For i = LBound(exp) To UBound(exp)
                If InStr(exp(i), "CO") > 0 Then
                    newSh.Cells(Rows.Count, 1).End(xlUp)(2) = Right(exp(i), _
                    Len(exp(i)) - (InStr(exp(i), "CO") - 1))
                ElseIf InStr(exp(i), "LOTD") > 0 Then
                    newSh.Cells(Rows.Count, 1).End(xlUp)(2) = Right(exp(i), _
                    Len(exp(i)) - (InStr(exp(i), "LOTD") - 1))
                End If
            Next
        End If
    Next
End Sub

 

[algere]

JLGWhiz, that's a great start - I really appreciate it! If there is more than one keyword "LOTD/CO" in each cell, is there a way to keep them grouped together or on adjacent lines (A1, B1, C1 instead of A1, A2, A3)? Doing it the way the code is currently written, though brilliant, puts each entry vertically on a new row and I cannot correlate the entry to it's original line.

 

[Matt Rogers]

Wow !!!

 

[JLGWhiz]

This post then to the same row, separate cells.

Sub wildhair()
Dim sh As Worksheet, newSh As Worksheet, lr As Long, rng As Range, exp As Variant, r As Long, x As Long
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A2:A" & lr)
Set newSh = Sheets.Add(After:=Sheets(Sheets.Count))
    For Each c In rng
        If InStr(c.Value, "CO") > 0 Or InStr(c.Value, "LOTD") > 0 Then
            r = newSh.Cells(Rows.Count, 1).End(xlUp).Row + 1
            exp = Split(c.Value, ".")
            For i = LBound(exp) To UBound(exp)
                If InStr(exp(i), "CO") > 0 Then
                    x = x + 1
                    newSh.Cells(r, x) = Right(exp(i), _
                    Len(exp(i)) - (InStr(exp(i), "CO") - 1))
                    ElseIf InStr(exp(i), "LOTD") > 0 Then
                    x = x + 1
                    newSh.Cells(r, x) = Right(exp(i), _
                    Len(exp(i)) - (InStr(exp(i), "LOTD") - 1))
                End If
            Next
        End If
        x = 0
    Next
 NewSh.Columns.Autofit
End Sub

 

[algere]

This is perfect JLGWhiz! Genius work! Now all I have to do is filter for the keywords "CO/LOTD" that do not have a space after the date because it exports the whole sentence. Even so, this saves me from pulling an "all-nighter' hunting and pecking to fix the spreadsheet! LOL Thanks for your help!

 

[algere]

JLGWhiz, one last question, (thanks for being so patient with me); someone used the "Alt/Enter command" for line break in each cell. Is there a way to clear those so that I don't have to go in manually to remove them?
[h=2][/h]

 

[algere]

JLGWhiz, I figured it out using a formula in the adjacent cell: =SUBSTITUTE($I2,CHAR(10)," ")

Thanks again for your help!