macro that would erase part of data in a cell?

**tradewindrider** · 12-17-2007, 12:07 PM

Is there a way to write a macro that would erase some data inside a cell?

I have a column that contain the following data:

8/08/2007 08:35:31 AM IST (GMT +05:30)
10/08/2007 08:40:10 AM IST (GMT +05:30)
10/08/2007 08:40:10 AM IST (GMT +05:30)
10/08/2007 08:40:10 AM IST (GMT +05:30)
15/11/2005 07:17:15 AM IST (GMT +05:30)
15/11/2005 07:17:15 AM IST (GMT +05:30)
15/11/2005 07:17:15 AM IST (GMT +05:30)

And I would like the above data become:

8/08/2007
10/08/2007
10/08/2007
10/08/2007
15/11/2005
15/11/2005
15/11/2005

Please help because I have to edit data 1 by 1 everyday, and I have to do that more than 500 times. >.<

Thank you for your assistance in advance

Clair

**davesexcel** · 12-17-2007, 12:20 PM

try to test this on a different workbook
place your data in column A and run this code

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 12/17/2007 by Dave Morrison
'

'
    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _
        TrailingMinusNumbers:=True
    Columns("B:F").Delete Shift:=xlToLeft
        Columns("A:A").EntireColumn.AutoFit

End Sub

**tradewindrider** · 12-17-2007, 12:50 PM

Oh my god! It work wonders! /love Many thankee thankee!

I have 1 more question, the data now look like this:

08/08/07
08/10/07
08/10/07
08/10/07
15/11/2005
15/11/2005
15/11/2005
15/11/2005
15/11/2005
15/11/2005
15/11/2005
18/05/2007
18/05/2007
18/05/2007
03/10/07
03/10/07
03/10/07
03/10/07
18/10/2007
15/11/2005
15/11/2005

How do I get the cell format into DD/MM/YYYY ? I have tried using Format Cell, Date and selected the format that I want. Only part of the data got their format changed. Why is that?

Many many thanks!

Clair

**Norie** · 12-17-2007, 12:55 PM

Clair

Try this amendment to what Dave posted.

Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 4), Array(2, 9), Array(3, 9), Array(4, 9), Array(5, 9), Array(6, 9))

**davesexcel** · 12-17-2007, 12:59 PM

Not sure why, try tagging another code to the end of the original

'
' Macro5 Macro
' Macro recorded 12/17/2007 by Dave Morrison
'

'
    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _
        TrailingMinusNumbers:=True
    Columns("B:F").Delete Shift:=xlToLeft
        Columns("A:A").EntireColumn.AutoFit
    Columns("A:A").NumberFormat = "dd/mm/yyyy "

End Sub