Need VBA, this one is a challenge

[skyport]

Assuming source data are in cells in column A. Need to find all dates (format xx/xx/xx) in each of the cells in column A. Then choose only those dates that are proceeded by a comma then a space, no other dates to be included. Then place the LAST 10 characters proceeding the comma in a separate cell in column B and along side of that, the applicable date in column C. This should create a column in B of different 10 digit entries (one entry of 10 digits per each cell). Along side of that, there would be the list in column C of the dates.

Mr. Excel has never failed to find a solution for me in the past. My compliments always to those that help. Please help if you can. Thanks

 

[Rick Rothstein]

Assuming source data are in cells in column A. Need to find all dates (format xx/xx/xx) in each of the cells in column A. Then choose only those dates that are proceeded by a comma then a space, no other dates to be included. Then place the LAST 10 characters proceeding the comma in a separate cell in column B and along side of that, the applicable date in column C. This should create a column in B of different 10 digit entries (one entry of 10 digits per each cell). Along side of that, there would be the list in column C of the dates.

Mr. Excel has never failed to find a solution for me in the past. My compliments always to those that help. Please help if you can. Thanks

Can you show us some examples of the kinds of text that is in Column A (both that meet your criteria and that do not meet your criteria)?

 

[skyport]

Rick, great to see you here again. I was hoping you would show up on this one. You never fail a good solution. To keep it simple, the data is not important. the key is focus on the last 10 digits before the comma, ONLY when the date has a comma then sppace before date what ever they be, to show up in column B and the respective date along side it in column C

Sample of accepted data to recognize: "Physicians report, Dr. XYZ, 04-24-13. "
The above would select out the circumstances that apply to a Doctor report and indicate who they are.

Sample of those excluded: "The patient went to the doctor on 09-25-14." (Note how there is by default no comma and therefore would be excluded)

 

[Rick Rothstein]

Rick, great to see you here again. I was hoping you would show up on this one. You never fail a good solution. To keep it simple, the data is not important. the key is focus on the last 10 digits before the comma, what ever they be, to show up in column B and the respective date along side it in column C

It has been my experience that "keeping it simple" usually means the poster will be back for an expanded solution when the solution to the simple problem fails to work correctly on the actual data. For example, what do your dates look like (12/11/2015... December 11, 2015... 11th of December... etc., etc.)? Will there be cells with dates without a comma/space before them? Will there be comma/space characters before the comma/space characters that are in front of the date? Will there be cells with comma/space and no date? Could there be a comma/space with text after it and then a date without a comma/space in front of it? Those are just some of the question (off the top of my head) I have that would affect the kind of solution I might come up with. It would really be useful if you could post a representative sampling of the kind of data our solution would have to work with and work around depending if the criteria was met or not.

 

[skyport]

Rick, you have never failed to resolve an issue for me - Ever. I have never needed to come back. A tribute to your solutions. Note that I already covered the date issue in the original posting. It is a 4 digit date format. The key to the other questions you posed would be if a date comes after the comma combined with the space right after, it would be included in the process. If not, should be ignored regardless. The solution would simply pick out any occurrence of - comma spacebar followed by a date. Once seen, it would deposit the applicable date - 10 digits preceeding the comma in column B and the date in column C

 

[Peter_SSs]

Perhaps I could give two reasons why Rick wants to see a good representative set of sample data.

1.

I need a VBA code that will eliminate the first character only in each cell and only when that first character in the cell is a blank space or a colon ( : )or 0 (zero-numerical).

What I didn't count on was multiples of the specified characters at the beginning of the cell. For example, a cell might contain

: 09/22/2015


in that example there are 5 of the specified characters before the data to be retained

2.

Need to find all dates (format xx/xx/xx)...

Sample of accepted data to recognize: "Physicians report, Dr. XYZ, 04-24-13. "

That date is not in the format previously quoted.


So why not give us 8-10 representative samples and the corresponding expected results?

 

[skyport]

Peter, Thanks for joining in. 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. the data prior to the commas is so vastly diverse, it cannot be pinpointed. The examples I gave above in post 3 are as pinpointed as can be I think. 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.

 

[skyport]

SAMPLE ILLUSTRATION:

Source: Primary Physician Report, Peter Jones, M.D., 12/06/14
Would distribute as follows:

Cell in B - ones, M.D.
Cell in C - 12/06/14


Sample 2:

Source: She went to the office on 12/13/14 and received treatment
This would be ignored as it has no comma-spacebar -date sequence to flag it.

With this being said, it would probably best for the code to recognize 12 characters prior to the comma instead of the 10 originally suggested

 

[skyport]

It might help to also see that each occurrence of a comma-spacebar-date found in the source would result in a separate entry on cells in both B&C

EXAMPLE:

[TABLE="width: 220"]
<tbody>[TR]
[TD] B[/TD]
[TD] C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]r jones, M.D.[/TD]
[TD="align: right"]12/6/2014[/TD]
[/TR]
[TR]
[TD]next occurance[/TD]
[TD]next date[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
</tbody>[/TABLE]

The above example on line one column B shows 13 characters included prior to the comma as the 12th character is a spacebar and my not be observed in the illustration. The final code may be best if as in the past solutions the line that shows where that count is dealt with could be altered later on such as from 12-14 characters.

 

[Rick Rothstein]

It might help to also see that each occurrence of a comma-spacebar-date found in the source would result in a separate entry on cells in both B&C

What exactly does the above mean? Are you trying to indicate that there could be more than one comma/space/date in a single cell? Hint: Saying "next occurrence" "next date" is not really all that clear when the next cell would contain a "next occurrence" and "next date". Here is the code I came up with assuming one comma/space/date per cell...

Sub GetTextCommaSpaceDate()
  Dim R As Long, X As Long, Data As Variant, Result As Variant
  Const CharsPriorToCommaSpace As Long = 12
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  ReDim Result(1 To UBound(Data), 1 To 2)
  For R = 1 To UBound(Data)
    If Data(R, 1) Like "*, ##/##/##*" Then
      For X = 1 To Len(Data(R, 1))
        If Mid(Data(R, 1), X, 10) Like ", ##/##/##" Then
          Result(R, 1) = Right(Left(Data(R, 1), X - 1), CharsPriorToCommaSpace)
          Result(R, 2) = CDate(Mid(Data(R, 1), X + 2, 8))
        End If
      Next
    End If
  Next
  Range("B1").Resize(UBound(Result), 2) = Result
End Sub