Pattern Matching with widcard and then some

[jfarc]

I've read all the previous 'Pattern Matching' posts and tried a bunch of things, but not getting anywhere yet. I think one problem is there are asterisks as part of my real data.

Below is the sample piece of data that is found in a column of cells - each cell containing what is in this sample below, all variable length data, but a portion of it with a distinctive pattern, of which I want to break apart:

   *** 09:23:20  18 JUL 2013 bobj JC-FI BOB_JETER ***   THIS IS THE 4TH COMMENT. I AM TYPING IN AT LINE7 ON THE 'M' SCREEN   *** 09:20:53  18 JUL 2013 davidf JC-FI DAVE_FRANKLIN ***   THIS IS THE 3RD COMMENT ENTERED.   *** 09:19:03  18 JUL 2013 deang JC-FI DEAN_GIFFORD ***   THIS IS THE 2ND COMMENT.   *** 09:16:20  18 JUL 2013 dealerp JC-FI DEALERPAIR ***   USING THIS DEAL TO TEST THE COMMENTS SECTION.

In the above example, you can see the distinct pattern of the (3 asterisks 8 digit time 11digit date userid location code username) all surrounded by 3 asterisks on ether end. This pattern marks the beginning of each comment that I would like to break out.

In the end I would like to break out the above single cell into multiple variables as follows:

variable1 = "*** 09:23:20  18 JUL 2013 bobj JC-FI BOB_JETER ***"
variable2 = "THIS IS THE 4TH COMMENT. I AM TYPING IN AT LINE7 ON THE 'M' SCREEN"
variable3 = "*** 09:20:53  18 JUL 2013 davidf JC-FI DAVE_FRANKLIN ***"
variable4 = "THIS IS THE 3RD COMMENT ENTERED."
variable5 = "*** 09:19:03  18 JUL 2013 deang JC-FI DEAN_GIFFORD ***"
variable6 = "THIS IS THE 2ND COMMENT."
variable7 = "*** 09:16:20  18 JUL 2013 dealerp JC-FI DEALERPAIR ***"
variable8 = "USING THIS DEAL TO TEST THE COMMENTS SECTION."

All this is variable meaning there could be only a single Time/Date stamp with comment or up to 10+ Time/Date stamp with comments. Doing this in VBA is much desirable. At least a push in the right direction would be helpful, thanks.

Another pattern maybe not as evident that I see is there are always 3spaces before the beginning 3asterisks and after the ending 3asterisks of each Time/Date stamp.

 

[Andrew Poulsom]

Maybe you can adapt this:

Sub Test()
    Dim AllText As String
    Dim SplitText As Variant
    Dim i As Long
    AllText = "   *** 09:23:20  18 JUL 2013 bobj JC-FI BOB_JETER ***   THIS IS THE 4TH COMMENT. I AM TYPING IN AT LINE7 ON THE 'M' SCREEN   *** 09:20:53  18 JUL 2013 davidf JC-FI DAVE_FRANKLIN ***   THIS IS THE 3RD COMMENT ENTERED.   *** 09:19:03  18 JUL 2013 deang JC-FI DEAN_GIFFORD ***   THIS IS THE 2ND COMMENT.   *** 09:16:20  18 JUL 2013 dealerp JC-FI DEALERPAIR ***   USING THIS DEAL TO TEST THE COMMENTS SECTION."
    SplitText = Split(AllText, "   ")
    For i = LBound(SplitText) To UBound(SplitText)
        Debug.Print SplitText(i)
    Next i
End Sub

 

[jfarc]

Thanks Andrew. In looking at live data the User entered Comments had numerous 3spaces occurrences, looks like program generated though.

I have found a solution. I am simply using the following Replace() code to replace each of the delimiters with the altEnter inside the original cell formatted as Wrap and it gives me my desired final end results:

    Selection.Replace What:="   ~*~*~* ", Replacement:=Chr(10), LookAt:=xlPart
    Selection.Replace What:="~*~*~*   ", Replacement:=Chr(10), LookAt:=xlPart

 

[jfarc]

Hello Andrew, I thought I found a solution, but ran into an issue in the next part of my project, which may result in the resolution to this to be revisited.

See post - http://www.mrexcel.com/forum/excel-...ly-portions-text-single-cell.html#post3524267