Macro for Text to Columns

[gammaves]

Whenever Google Analytics exports a date, it shows up as

Date

20220117

20220118

20220101

20220102

20211227

The best way to make it into a format usable by excel is to use the Text to columns button.
I chose 'Delimited', but uncheck all of the different delimiters. Then on the Column Data Format page of it, I choose date.

I wanted to create a macro that looks for a column with the header "Date" and then applies this formatting to it.

Thank you for helping me out!

 

[Joe4]

Try this (assuming row 1 is your header row):

Sub MyDateMacro()

'   Find column with the word "Date" in row 1
    Rows("1:1").Find(What:="Date", After:=Range("A1"), LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

'   Split column
    ActiveCell.EntireColumn.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 5), TrailingMinusNumbers:=True

End Sub

 

[gammaves]

Try this (assuming row 1 is your header row):

Sub MyDateMacro()

'   Find column with the word "Date" in row 1
    Rows("1:1").Find(What:="Date", After:=Range("A1"), LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

'   Split column
    ActiveCell.EntireColumn.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 5), TrailingMinusNumbers:=True

End Sub

Thanks Joe, It looks like it worked, but it put the proper dates in the next column over, overwriting anything that was there. Is there a way for the destination to just overwrite whatever is in the Date column?

 

[Joe4]

Sorry, forgot to remove the hard-reference to cell D1.

Try this:

Sub MyDateMacro()

'   Find column with the word "Date" in row 1
    Rows("1:1").Find(What:="Date", After:=Range("A1"), LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

'   Split column
    ActiveCell.EntireColumn.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 5), TrailingMinusNumbers:=True

End Sub

 

[Joe4]

Here is a more robust version that will tell you it cannot find the word "Date" instead of returning an error, if it is possible the word may not appear on row 1:

Sub MyDateMacro()

    On Error GoTo err_chk

'   Find column with the word "Date" in row 1
    Rows("1:1").Find(What:="Date", After:=Range("A1"), LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

'   Split column
    ActiveCell.EntireColumn.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 5), TrailingMinusNumbers:=True
        
    Exit Sub
    
err_chk:
    If Err.Number = 91 Then
        MsgBox "Cannot find the word 'Date' in row 1", vbOKOnly
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If
    
End Sub

 

[gammaves]

That did it! Thanks a lot!

 

[Joe4]

You are welcome.