VBA date clean up of Excel data

K

kdrymer

New Member
Joined
Apr 19, 2012
Messages
12
I have some date data that I want to clean up and remove any text that is in the date.
I have the following code that outputs data to a worksheet, and it has a separate datecleanup function that does some of the date cleanup if there is a missing date, or it is only 4 digits, however I am still getting data outputted that contains a mixture of dates and text (examples below).


Code: 
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">[COLOR=#101094][FONT=inherit]Function[/FONT][/COLOR][COLOR=#303336][FONT=inherit] TetanusLoad[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]col [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]String[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] col2 [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]String[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] i [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Long[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] j [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Long[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] k [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Long[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

j [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Worksheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"CI"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"A"[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Rows[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Count[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]xlUp[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Row [/FONT][/COLOR][COLOR=#303336][FONT=inherit]+[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
k [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Worksheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Error"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"A"[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Rows[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Count[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]xlUp[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Row [/FONT][/COLOR][COLOR=#303336][FONT=inherit]+[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

[/FONT][/COLOR][COLOR=#101094][FONT=inherit]For[/FONT][/COLOR][COLOR=#303336][FONT=inherit] i [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]2[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]To[/FONT][/COLOR][COLOR=#303336][FONT=inherit] lstrow

[/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Len[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]Worksheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Data"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]col [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] i[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]And[/FONT][/COLOR][COLOR=#303336][FONT=inherit] 
Len[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]Worksheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Data"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]col2 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] i[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Then[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]GoTo[/FONT][/COLOR][COLOR=#303336][FONT=inherit] EmptyRange
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Else[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
  strDate [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] spacedate[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]Worksheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Data"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]col [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] i[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
  Worksheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"CI"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"A"[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] j [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]":C"[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] j[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] 
   Worksheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Data"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"F"[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] i [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]":H"[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] i[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value

[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Select[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Case[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Worksheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Data"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]col2 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] i[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Case[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Tdap"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        Worksheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"CI"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"D"[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] j[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"TDA"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Case[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Td"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        Worksheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"CI"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"D"[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] j[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"TD"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Case[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Else[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        Worksheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"CI"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"D"[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] j[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"REVIEW"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Select[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

Worksheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"CI"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"E"[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] j[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] datecleanup[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]strDate[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

 j [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] j [/FONT][/COLOR][COLOR=#303336][FONT=inherit]+[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

EmptyRange[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Next[/FONT][/COLOR][COLOR=#303336][FONT=inherit] i

[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Function
[/FONT][/COLOR]</code>

datecleanup function:
Code: 
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">[COLOR=#101094][FONT=inherit]Function[/FONT][/COLOR][COLOR=#303336][FONT=inherit] datecleanup[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]inputdate [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Variant[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Variant[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

[/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Len[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]inputdate[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Then[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
  inputdate [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"01/01/1901"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Else[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
 [/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Len[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]inputdate[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]4[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Then[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    inputdate [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"01/01/"[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] inputdate
 [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Else[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit] InStr[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] inputdate[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"."[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Then[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        inputdate [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Replace[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]inputdate[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"."[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"/"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
 [/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

datecleanup [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] inputdate

[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Function[/FONT][/COLOR]</code>

Sample data output examples for column E that I am trying to correct:
Code: 
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">[COLOR=#7D2727][FONT=inherit]07[/FONT][/COLOR][COLOR=#303336][FONT=inherit]/[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]06[/FONT][/COLOR][COLOR=#303336][FONT=inherit]/[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1993[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit]-[/FONT][/COLOR][COLOR=#303336][FONT=inherit] HAD ALLERGIC REACTION [/FONT][/COLOR][COLOR=#303336][FONT=inherit];[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ARM SWELLED [/FONT][/COLOR][COLOR=#101094][FONT=inherit]AND[/FONT][/COLOR][COLOR=#303336][FONT=inherit] GOT RED [/FONT][/COLOR][COLOR=#101094][FONT=inherit]AND[/FONT][/COLOR][COLOR=#303336][FONT=inherit] HOT
[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]09[/FONT][/COLOR][COLOR=#303336][FONT=inherit]/[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]23[/FONT][/COLOR][COLOR=#303336][FONT=inherit]/[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]2004[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit]-[/FONT][/COLOR][COLOR=#303336][FONT=inherit] REPORTS REACTION [/FONT][/COLOR][COLOR=#101094][FONT=inherit]TO[/FONT][/COLOR][COLOR=#303336][FONT=inherit] TETANUS SHOT
[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]12[/FONT][/COLOR][COLOR=#303336][FONT=inherit]/[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]03[/FONT][/COLOR][COLOR=#303336][FONT=inherit]/[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]2015[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Rubelo reported[/FONT][/COLOR]</code>

I don't want the additional text included, as this should be a date only field. How can I accomplish this? Ideally I would like it to be referenced in the datecleanup function as other functions use this as well.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Hi,
try changing this line

Code: 
DateCleanup = inputdate

to this

Code: 
DateCleanup = DateValue(Left(inputdate, 10))

and see if this does what you want

Dave
 
Upvote 0
@dmt32 Thanks Dave, will this work if there is text before the date? I have some days where there is text and then a date. Thanks again for your help!
 
Last edited:
Upvote 0
kdrymer said:
@dmt32 Thanks Dave, will this work if there is text before the date? I have some days where there is text and then a date. Thanks again for your help!
Click to expand...

No - suggestion was based on sample data you posted.

Suggest that you post examples of other likely data outputs & perhaps I or another here can make suggestion to modify your datecleanup function to accommodate.

Dave
 
Last edited:
Upvote 0
dmt32 said:
No - suggestion was based on sample data you posted.

Suggest that you post examples of other likely data outputs & perhaps I or another here can make suggestion to modify your datecleanup function to accommodate.

Dave
Click to expand...

Sorry about that, Below are examples of the date/text field I am trying to extract just the date from. There are some with multiple dates in the string and I am not too worried about programmatically fixing these, however the strings with a single date I would like to extract from the rest of the text. Thanks for and help you can offer!

[TABLE="width: 730"]
<colgroup><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 638"]
<colgroup><col></colgroup><tbody>[TR]
[TD]8/20/91 RUBEOLA[/TD]
[/TR]
[TR]
[TD]6/1/90 MEASLES[/TD]
[/TR]
[TR]
[TD]4/6/92 MEASLES RECIEVED MUMPS VACCINE 12/81[/TD]
[/TR]
[TR]
[TD]02/18/1987 - MMR #3 03/31/2006[/TD]
[/TR]
[TR]
[TD]05/04/1970 RUBEOLA - MUMPS VACCINE 09/13/1975[/TD]
[/TR]
[TR]
[TD]MUMPS TITER 02/26/2008 POSITIVE[/TD]
[/TR]
[TR]
[TD]01/18/93 See Titers[/TD]
[/TR]
[TR]
[TD]5/1/80 - MMR #3 5/1/90 - MMR #4 12/18/01[/TD]
[/TR]
[TR]
[TD]Mumps-2/12/73; MMR #2 : 6/30/06 Measles - 4/25/72 Rubella - 3/22/75[/TD]
[/TR]
[TR]
[TD]05/01/1977 - MUMPS TITER 09/16/2008 POSITIVE[/TD]
[/TR]
[TR]
[TD] Measles - 11/10/71 Rubella - 12/19/72 Mumps - 12/6/73[/TD]
[/TR]
[TR]
[TD]MUMPS TITER 4/15/09 POSITIVE[/TD]
[/TR]
[TR]
[TD]MUMPS TITER 10/08/2008 POSITIVE[/TD]
[/TR]
[TR]
[TD]1/20/90 - MMR #3 6/23/15[/TD]
[/TR]
[TR]
[TD]MUMPS TITER POSITIVE 05/28/2013 [/TD]
[/TR]
[TR]
[TD]8/25/92; 6/11/17[/TD]
[/TR]
[TR]
[TD]06/23/1995 - MMR #3 10/04/2011[/TD]
[/TR]
[TR]
[TD]5-28-10 Mumps titer - Positive[/TD]
[/TR]
[TR]
[TD]12/12/69 - MUMPS TITER 12/23/09 POSITIVE[/TD]
[/TR]
[TR]
[TD] 08/23/1976 RUBEOLA VACCINE[/TD]
[/TR]
[TR]
[TD]9/17/65 MEASLES - MMR #3 1/15/07 - POSITIVE HX OF MUMPS[/TD]
[/TR]
[TR]
[TD]MUMPS TITER 2/14/14 POSITIVE[/TD]
[/TR]
[TR]
[TD]06/23/2011 - MUMPS TITER 06/20/2011 POSITIVE[/TD]
[/TR]
[TR]
[TD]11/09/1963 BOTH MEASLES VACCINE [/TD]
[/TR]
[TR]
[TD]7/3/90 - MMR #3 7/9/08[/TD]
[/TR]
[TR]
[TD]03/16/1994 - MMR #3 07/03/2012 [/TD]
[/TR]
[TR]
[TD]10/23/1967 - MEASLES / RUBELLA VACCINE[/TD]
[/TR]
[TR]
[TD]5-26-05 Mumps titer - Immune[/TD]
[/TR]
[TR]
[TD]10/21/2008 MUMPS TITER POSITIVE[/TD]
[/TR]
[TR]
[TD] 4/24/73 - MUMPS TITER 3/16/06 POSITIVE[/TD]
[/TR]
[TR]
[TD]4/22/09, MUMPS TITER 7/7/11 POSITIVE[/TD]
[/TR]
[TR]
[TD]8/17/11, MUMPS TITER POSITIVE[/TD]
[/TR]
[TR]
[TD]Mumps titer 8-7-15 - Positive, MMR - 5-22-87[/TD]
[/TR]
[TR]
[TD]2/25/91 RUBEOLA VACCINE[/TD]
[/TR]
[TR]
[TD]11/17/71 RUBEOLA[/TD]
[/TR]
[TR]
[TD]Rubeola vaccine- 8/22/91 & 2/27/92[/TD]
[/TR]
[TR]
[TD]6/1/76 RUBEOLA[/TD]
[/TR]
[TR]
[TD]2/23/76 RUBEOLA[/TD]
[/TR]
[TR]
[TD]5/18/09 MUMPS TITER: POSITIVE[/TD]
[/TR]
[TR]
[TD]5/22/70 RUBEOLA[/TD]
[/TR]
[TR]
[TD]5/1/68 MEASLES[/TD]
[/TR]
[TR]
[TD]11/3/83 RUBEOLA 11/30/10 MUMPS TITER POSITIVE[/TD]
[/TR]
[TR]
[TD]6/21/77 MMR #3 11/18/15[/TD]
[/TR]
[TR]
[TD]9/11/86 MMR #3 3/10/04[/TD]
[/TR]
[TR]
[TD]6/5/80 MR[/TD]
[/TR]
[TR]
[TD]1/30/92 RUBEOLA [/TD]
[/TR]
[TR]
[TD]2/25/91 BOTH RUBEOLA[/TD]
[/TR]
[TR]
[TD]2/25/91 #2 RUBEOLA[/TD]
[/TR]
[TR]
[TD]3/1/78 RUBEOLA[/TD]
[/TR]
[TR]
[TD]2/28/91 #2 RUBEOLA[/TD]
[/TR]
[TR]
[TD]4/21/97 BOTH MEASLES[/TD]
[/TR]
[TR]
[TD]2/28/92 RUBEOLA[/TD]
[/TR]
[TR]
[TD]11/25/91 RUBEOLA #3 MMR 6/8/99[/TD]
[/TR]
[TR]
[TD]1/11/66 - MMR #3 4/1/68 - MMR #4 4/1/76[/TD]
[/TR]
[TR]
[TD]2/8/99 - MUMPS TITER 9/19/08 POSITIVE[/TD]
[/TR]
[TR]
[TD]1/26/78 - MMR #3 8/1/99[/TD]
[/TR]
[TR]
[TD]4/5/72 RUBEOLA[/TD]
[/TR]
[TR]
[TD]MUMPS TITER 4/23/13 POSITIVE - MUMPS VACCINE 6/22/92 - SMALLPOX VACCINE 1/1/63[/TD]
[/TR]
[TR]
[TD]2/28/92 RUBEOLA[/TD]
[/TR]
[TR]
[TD]8/14/03 RUBEOLA - 5/1/72 RUBELLA VACCINE[/TD]
[/TR]
[TR]
[TD]6/22/85 - MMR #3 6/22/99[/TD]
[/TR]
[TR]
[TD]4/12/74 BOTH RUBEOLA[/TD]
[/TR]
[TR]
[TD]10/1/68 MEASLES VACCINE[/TD]
[/TR]
[TR]
[TD]2/18/76 RUBEOLA[/TD]
[/TR]
[TR]
[TD]2/19/76 RUBEOLA - POSITIVE HX OF MUMPS[/TD]
[/TR]
[TR]
[TD]6/7/76 - MUMPS VACCINE 5/24/77[/TD]
[/TR]
[TR]
[TD]1/1/66 BOTH MEASLES VACCINE - MUMPS TITER 2/14/80 POSITIVE[/TD]
[/TR]
[TR]
[TD]2/1/71 RUBEOLA[/TD]
[/TR]
[TR]
[TD]8/22/91 BOTH RUBEOLA[/TD]
[/TR]
[TR]
[TD]3/10/66 BOTH MEASLES VACCINE[/TD]
[/TR]
[TR]
[TD]5/24/94 - SMALLPOX VACCINE 3/1/03 & 3/10/13[/TD]
[/TR]
[TR]
[TD]05/04/2010 MEASLES VACCINE - MUMPS TITER 06/07/2007 POSITIVE[/TD]
[/TR]
[TR]
[TD]10/06/1972 MR VACCINE[/TD]
[/TR]
[TR]
[TD]POSITIVE HX OF MEASLES 1967 - POSITIVE HX OF MUMPS 1973; 12/16/16 Mumps antibody titer- positive[/TD]
[/TR]
[TR]
[TD]09/01/1974 MEASLES VACCINE[/TD]
[/TR]
[TR]
[TD]08/22/1991 RUBEOLA[/TD]
[/TR]
[TR]
[TD]08/22/1991 BOTH RUBEOLA VACCINES[/TD]
[/TR]
[TR]
[TD]HEPATITIS C TITER 12/10/2002 NEGATIVE[/TD]
[/TR]
[TR]
[TD]02/28/1992 RUBEOLA[/TD]
[/TR]
[TR]
[TD]08/21/1991 BOTH RUBEOLA[/TD]
[/TR]
[TR]
[TD]02/13/1980 RUBEOLA[/TD]
[/TR]
[TR]
[TD]10/16/1968 - MMR #3 06/02/1990[/TD]
[/TR]
[TR]
[TD]MUMPS TITER 01/20/2012 POSITIVE[/TD]
[/TR]
[TR]
[TD]RUBELLA VACCINE GIVEN 10/22/2001[/TD]
[/TR]
[TR]
[TD]03/02/1994 & 06/09/1994 RUBELLA VACCINES[/TD]
[/TR]
[TR]
[TD]MUMPS TITER POSITIVE 07/20/2006[/TD]
[/TR]
[TR]
[TD]2-20-64 Measles vaccine, 3-17-70 Rubella vaccine, 7-1-80 Measles vaccine[/TD]
[/TR]
[TR]
[TD]1-11-15, SEE TITERS[/TD]
[/TR]
[TR]
[TD]9-19-06 Mumps titer - Positive[/TD]
[/TR]
[TR]
[TD]8/30/12 - Mumps Titer - POSITIVE[/TD]
[/TR]
[TR]
[TD]5-1-73 Measles[/TD]
[/TR]
[TR]
[TD]5-11-73 Rubella vaccine and Rubeola vaccine[/TD]
[/TR]
[TR]
[TD]2-28-92 Measles vaccine[/TD]
[/TR]
[TR]
[TD] 4/18/74[/TD]
[/TR]
[TR]
[TD]10/15/80; 3rdMMR- 6/1/1996[/TD]
[/TR]
[TR]
[TD]MUMPS antibody titer- 5/26/15- Positive[/TD]
[/TR]
[TR]
[TD]Age Exempt[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA code clean up
Replies
9
Views
536
mumps
mumps
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
570
smozgur
smozgur
E
  • Question Question
Adding a custom message.
Replies
1
Views
645
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top