Sorry, I'm returning to the 'admonishment' for a moment, hopefully to help clarify for the future. I will then offer other suggestions here.
Those previous examples you gave have no relevancy to this situation. This one would stand on it's own as a function without the affect of the previous solutions you referred to.
I think you may have missed my point. My points at the start of post #6 were supporting Rick's assertion that posters who give a brief description without
representative data, usually have to come back for a modified solution when the given solution fails on their real data.
My point 1. was simply using yourself as a good example, where in that other thread I quoted from you made a statement of what was required, got some solutions that did exactly that, then returned to say that your requirement actually wasn't what you had first stated. If we had seen a set of
representative data in that thread we
may have asked the question about repeated characters
before spending time developing a solution.
The examples I gave above in post 3 are as pinpointed as can be I think.
Are they? You never addressed my point 2. in post #6. The dates in post #3 contain "-" whereas post #1 used "/". The fact that Rick's code apparently does what you want makes me
think you don't have any dates like "04-24-13", but
representative data
might have confirmed that one way or the other.
I would think in my limited understanding that if the code could search and find ANY occurrence, anywhere in the source of "comma-spacebar-date, then deliver the previous 10 characters prior to the comma, regardless of what they are, into cell in B and the date in C, that would do it.
One of the problems we often encounter is the OP deciding how
they would attack the problem & give information based only on that rather than giving helpers what they want to know because they might have several different possible methods and would choose the most appropriate one based on the actual structure and variety of the data.
Anyway, I'm offering two further options.
The first is somewhat similar to Rick's but should be faster (not really relevant if your data is not very big & I don't think you have mentioned how much data there might be) as it 'skips' through the text in bigger chunks. It also uses the guess of a maximum of 5 relevant dates in each cell. It is different to Rick's in that it would also pick up any dates in "mm-dd-yy" format or say like "1 Mar 15". I don't know if you would want that or not or perhaps it just isn't relevant.
Rich (BB code):
Sub GetTCSD1()
Dim Data As Variant, Result As Variant, d As Variant
Dim r As Long, z As Long, pos As Long
Dim s As String
Const TextLen As Long = 12
Data = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Value
ReDim Result(1 To 5 * UBound(Data), 1 To 2)
For r = 1 To UBound(Data)
s = Data(r, 1)
Do
pos = InStr(pos + 1, s, ", ")
If pos > 0 Then
d = Mid(s, pos + 2, 8)
If IsDate(d) Then
z = z + 1
Result(z, 1) = Right(Left(s, pos - 1), TextLen)
Result(z, 2) = d
End If
End If
Loop Until pos = 0
Next r
Range("B1").Resize(z, 2) = Result
End Sub
The following is similar but allows for any number of relevant dates in a cell, however it is marginally slower than the code above.
Rich (BB code):
Sub GetTCSD2()
Dim Data As Variant, Result As Variant, d As Variant
Dim r As Long, z As Long, pos As Long
Dim s As String
Const TextLen As Long = 12
Data = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Value
ReDim Result(1 To 2, 1 To 1)
For r = 1 To UBound(Data)
s = Data(r, 1)
Do
pos = InStr(pos + 1, s, ", ")
If pos > 0 Then
d = Mid(s, pos + 2, 8)
If IsDate(d) Then
z = z + 1
ReDim Preserve Result(1 To 2, 1 To z)
Result(1, z) = Right(Left(s, pos - 1), TextLen)
Result(2, z) = d
End If
End If
Loop Until pos = 0
Next r
Range("B1").Resize(z, 2) = Application.Transpose(Result)
End Sub
