Need VBA to change date format in text of entire column

[skyport]

I have text in column A spread out over as much as a few hundred cells. The data contains normal sentences and paragraphs along with occasional dates which will show up in any one of four different formats in general being: M/D/YY, MM/DD/YY, M/D/YYYY and MM,DD,YYYY. What I need to be able to do is be able to run a code from a module that will allow me to choose which date format I want to use that particular day and change all dates that show up in column A to that one selected date format. I am not able to do this by simply formatting the cells because the source data comes from a word document with its own formatting that seems to prevent any Excel date formatting under the normal process I believe. Can anyone suggest a code that would do this?

 

[FDibbins]

I am not able to do this by simply formatting the cells

That is because your "dates" are not dates, they are text, and you cannot apply number formatting to text (date is a special kind of number).

Does your string contain any other numbers?
You state that ALL your dates are in some format of Month/Day/Year, correct?
Can you provide a small sample of what you have - and what you want?

 

[skyport]

Hi Ford, thanks for helping me with this. String may contain other numbers but it is not important what happens with those numbers and that data. The questions that you asked made me think that it is possible to take something I'm already using to make what I need simpler for you to help with. In each cell in column A, the only data I really need to deal with is the first sentence of each cell in column A and that first sentence is always ending with the first occurrence of a period followed by a space within that cell. All other data in that cell beyond that point is irrelevant and that is where other forms of numbers would occur to answer your question about other types of numbers. My point is that I am already using another VBA that takes just the first sentence of the data in each cell up to that point of the first occurrence of the period followed by a space and places that result in column B. That result in column B will always contain one date and no other numbers of any kind. So if it's easier to use that current VBA I'm using as a starting point, maybe something could be added to it to simply allow me to choose which date format I want the result in column B to reflect. The result that shows in column B will always contain three components that consist of one date, one name of a Dr., and a title of a report and nothing more. Immediately after those three components just mentioned will always occur that first occurrence of the period followed by the space marking the end of the data that needs to be considered within the code. All subsequent data in that cell would be irrelevant. Hopefully this could simplify the needed solution. Below is the current code I'm using along with examples of how that first sentence of data would look in column B using the current VBA. All that would be needed is a way for me to be able to select which format the date would show up as.

As an Example: Result in Col B would look like the following:

09/12/2000, Joe Smith, M.D., Primary physician Report. or, it might be in a different order such as Primary Report, Joe Smith, M.D., 09/12/2000.

I need to be able to choose between the following date formats each time I would run the code: M/D/YY, MM/DD/YY, M/D/YYYY and MM,DD,YYYY


Current code being used:

Sub FirstSentence()
Dim lastRow As Long, splitChars As String
'get the characters to split by from the sheet
'splitChars = Me.Range("splitString")
splitChars = ". "
With Sheets("headlines")
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("B1:B" & lastRow) = Evaluate(Replace(Replace("IF(@A1:A#="""","""",LEFT(@A1:A#,FIND("". "",@A1:A#&"". "")))", "#", lastRow), "@", .Name & "!"))
End With
End Sub

 

[skyport]

Bump

 

[Peter_SSs]

Could you give 4-5 samples values from column A, together with the expected results in column B if the date format was, say, "M/D/YY" and then the expected results from the same date if the date format was instead "MM,DD,YYYY"?

 

[skyport]

Peter, as always good to see you here. Thanks for your help.

Column A examples:

[TABLE="width: 782"]
<tbody>[TR]
[TD]03/02/13, Handwritten Primary Treating Physician Progress Report, Albert Smith, M.D. She complains of neck pain, rated 6/10, with radiation into the hands. She is taking 2 Norco tabs a day for pain relief. Diagnoses: Unchanged. Treatment plan: Medications were refilled. Cervical spine epidural steroid injection was pending. Work status: Unchanged.
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[/TR]
[TR]
[TD]03/25/13, Handwritten Primary Treating Physician Progress Report, Susan Berg, Ph.D. The patient complains of chronic head pain. She is feeling depressed, overwhelmed, and sad. Diagnoses: 1) Cervical strain; 2) Shoulder strain; and 3) PTSD. Treatment plan: Continue with weekly cognitive behavioral therapy. Work status: Remain off work until pain is effectively managed.
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[/TR]
[TR]
[TD]04//18/13, Handwritten Primary Treating Physician Progress Report, Susan Berg, Ph.D. The patient continues to complain of anxiety and depression. Diagnoses: Unchanged. Treatment plan: Continue with weekly supportive cognitive behavioral therapy. Work status: Not provided.
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[/TR]
[TR]
[TD]04/22/13, Handwritten Primary Treating Physician Progress Report, Albert Smith, M.D. The patient’s symptoms remain unchanged. Celebrex 100 mg p.o. q.d. #30 was started and naproxen and Prilosec are to be discontinued. Treatment plan: Follow-up consultation with a neurologist for evaluation of post-concussive syndrome per the recommendations of the AME and psychologist. Work status: Unchanged.


Column B results M/D/YY[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 892"]
<tbody>[TR]
[TD]3/2/13, Handwritten Primary Treating Physician Progress Report, Albert Smith, M.D.
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[/TR]
[TR]
[TD]3/25/13, Handwritten Primary Treating Physician Progress Report, Susan Berg, Ph.D.
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[/TR]
[TR]
[TD]4//18/13, Handwritten Primary Treating Physician Progress Report, Susan Berg, Ph.D.
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[/TR]
[TR]
[TD]4/22/13, Handwritten Primary Treating Physician Progress Report, Albert Smith, M.D.
[/TD]
[/TR]
</tbody>[/TABLE]


Column B results MM/DD/YYYY

[TABLE="width: 892"]
<tbody>[TR]
[TD]03/02/2013, Handwritten Primary Treating Physician Progress Report, Albert Smith, M.D.
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[/TR]
[TR]
[TD]03/25/2013, Handwritten Primary Treating Physician Progress Report, Susan Berg, Ph.D.
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[/TR]
[TR]
[TD]04//18/2013, Handwritten Primary Treating Physician Progress Report, Susan Berg, Ph.D.
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[/TR]
[TR]
[TD]04/22/2013, Handwritten Primary Treating Physician Progress Report, Albert Smith, M.D.
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

04//18/13, Handwritten Primary Treating Physician Progress Report, Susan Berg, Ph.D. The patient continues to complain of anxiety and depression. Diagnoses: Unchanged. Treatment plan: Continue with weekly supportive cognitive behavioral therapy. Work status: Not provided.

I'm assuming that was a typo.
Try this in a copy of your workbook.

Sub FormatDateInText()
  Dim Data
  Dim FormatType As Long, i As Long
  Dim FormatPattern As String
  Dim M As Object
  
  Const sPrompt As String = "1. M/D/YY" & vbLf & "2. MM/DD/YY" & vbLf & _
                            "3. M/D/YYYY" & vbLf & "4. MM,DD,YYYY"
  
  FormatType = Application.InputBox(Prompt:=sPrompt, Title:="Choose Date format by number", Type:=1)
  FormatPattern = Split(Split(sPrompt, vbLf)(FormatType - 1))(1)
  Data = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  With CreateObject("VBScript.RegExp")
    .Pattern = "(\d{1,2}\/){2}\d{2,4}"
    For i = 1 To UBound(Data)
      If .Test(Data(i, 1)) Then
        Set M = .Execute(Data(i, 1))
        Data(i, 1) = Replace(Left(Data(i, 1), InStr(1, Data(i, 1), ". ")), M(0), Format(M(0), FormatPattern))
      End If
    Next i
  End With
  Range("B2").Resize(UBound(Data)).Value = Data
End Sub

 

[skyport]

Peter, good catch on the typo. Thanks. Code functions awesome. 2 small notes:

1. I adjusted the following part.....

Const sPrompt As String = "1. M/D/YY" & vbLf & "2. MM/DD/YY" & vbLf & _
"3. M/D/YYYY" & vbLf & "4. MM,DD,YYYY"

....... so that all four selections would use a / between the month day and year instead of a comma.

2. the only other thing is, it does not process Cell A1 and show any result in B1. B1 remains blank. Otherwise it works fantastic.

 

[Peter_SSs]

1. I adjusted the following part.....

Const sPrompt As String = "1. M/D/YY" & vbLf & "2. MM/DD/YY" & vbLf & _
"3. M/D/YYYY" & vbLf & "4. MM,DD,YYYY"

....... so that all four selections would use a / between the month day and year instead of a comma.

Fair enough, I used the formats you gave in post #1.

2. the only other thing is, it does not process Cell A1 and show any result in B1. B1 remains blank. Otherwise it works fantastic.

I assumed headings in row 1. If data starts in row 1 then change the references to A2 and B2 in the code to A1 and B1.

 

[skyport]

You are absolutely right. I made the goof in post 1 with the commas. made your recommended adjustments and it works perfectly!! Thanks as always for the marvelous solution.