VBA Text to column code clean up

[Donai]

Hi, could someone please help me clean this code up, i am using delimiter to change Date Text to number

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("G6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range("G6"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
End Sub

 

[Donai]

Why do you need to convert those? They look like UK-style dates (dd/mm/yyyy) to me.

That actual format is correct but some of those dates are Text, i cant replicate the text dates in the screen shot.

When i do text to columns manually it works but if i record the same steps it does not work

 

[Donai]

Why do you need to convert those? They look like UK-style dates (dd/mm/yyyy) to me.

Vog, when i record macro via Fixed width it works but using delimiter does not work, why is this the case? What would you use? to convert Text dates to number?

Sub Macro3()
'
' Macro3 Macro
'

'
    Range("G6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("G6"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
End Sub

 

[VoG]

Doing it manually I would use Delimited.

 

[Donai]

Doing it manually I would use Delimited.

but using macro would you use fixth width?

 

[VoG]

If it works then of course

 

[GTO]

Hi Mark, this is the original data, this data came from MS Access

Excel Workbook
	G
5	Start Date
6	29/04/2009
7	06/04/2009
8	08/09/2010
9	22/03/2011
10	22/03/2011
11	04/03/2011
12	07/07/2011
13	08/03/2011
14	08/03/2011
15	22/03/2011
Office_Address_List

Hi again,

Its probably just me, but I am not understanding this: If the data is coming over like this, why are we using Text2Columns? We're not splitting anything, are we?

Sorry if I'm being thick-headed, but thought to ask.

Mark

 

[VoG]

Hi Mark.

TTC converts dates stored as text to Excel dates. To try it enter a date preceded by a ' then TTC, click Next twice, check date and select MDY then click Finish.

 

[Donai]

Hi again,

Its probably just me, but I am not understanding this: If the data is coming over like this, why are we using Text2Columns? We're not splitting anything, are we?

Sorry if I'm being thick-headed, but thought to ask.

Mark

Mark some of those dates is in Text format, i cant replicate this on the screen shot, therefore i used to Text to columns to convert to number, do you have any other suggestions?

 

[GTO]

Hi Mark.

TTC converts dates stored as text to Excel dates. To try it enter a date preceded by a ' then TTC, click Next twice, check date and select MDY then click Finish.

Hi Peter,

Thank you for that, I did indeed forget this. When I try this with the OP's supplied data (preface: American date default here, and to prevent it from converting the dates is recognizes, I changed numberformat to text before typing in the values...), I get:

Excel Workbook
	J
1	29/04/2009
2	6/4/2009
3	8/9/2010
4	22/03/2011
5	22/03/2011
6	4/3/2011
7	7/7/2011
8	8/3/2011
9	22/03/2011
Sheet3

Excel 2003

Mark some of those dates is in Text format, i cant replicate this on the screen shot, therefore i used to Text to columns to convert to number, do you have any other suggestions?

Okay, it sounds as though you may have solved it, but if not, I was thinking a quick stab might be: In a junk copy of your workbook, select the offending text/dates and try -

Sub exa()
Dim myRange As Range, Cell As Range
Dim aryTemp
    
    Set myRange = Selection
    
    For Each Cell In myRange
        If Cell.NumberFormat = "@" Or Cell.NumberFormat = "General" Then
            If Cell.Text Like "##/##/####" Then
                aryTemp = Split(Cell.Text, "/")
                Cell.NumberFormat = "dd/mm/yyyy"
                Cell.Value = DateSerial(aryTemp(2), aryTemp(1), aryTemp(0))
                
            End If
        End If
    Next
End Sub

Does that do anything (preferably anything good...) ?

Mark

 

[Donai]

Nope that did not work, i ran your code but nothing happened.

But this code definately works, i'm just gobsmaked that when recording steps for Delimiter the code does not work but recording steps via fixed width it works, i have no idead why delimiter would not work.

Private Sub Text()

Dim LR As Long

LR = Range("G" & Rows.Count).End(xlUp).Row
Range("G6:G" & LR).TextToColumns Destination:=Range("G6"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
End Sub

Hi Peter,

Thank you for that, I did indeed forget this. When I try this with the OP's supplied data (preface: American date default here, and to prevent it from converting the dates is recognizes, I changed numberformat to text before typing in the values...), I get:

Excel Workbook
	J
1	29/04/2009
2	6/4/2009
3	8/9/2010
4	22/03/2011
5	22/03/2011
6	4/3/2011
7	7/7/2011
8	8/3/2011
9	22/03/2011
Sheet3

Excel 2003





Okay, it sounds as though you may have solved it, but if not, I was thinking a quick stab might be: In a junk copy of your workbook, select the offending text/dates and try -

Sub exa()
Dim myRange As Range, Cell As Range
Dim aryTemp
    
    Set myRange = Selection
    
    For Each Cell In myRange
        If Cell.NumberFormat = "@" Or Cell.NumberFormat = "General" Then
            If Cell.Text Like "##/##/####" Then
                aryTemp = Split(Cell.Text, "/")
                Cell.NumberFormat = "dd/mm/yyyy"
                Cell.Value = DateSerial(aryTemp(2), aryTemp(1), aryTemp(0))
                
            End If
        End If
    Next
End Sub

Does that do anything (preferably anything good...) ?

Mark