Well, actually, use
Range("A3:A6").TextToColumns Destination:=Range("A3"), DataType:=1,
TextQualifier:=1, FieldInfo:=Array(1, 4), TrailingMinusNumbers:=True
Sorry for posting without testing the previous one.
Regards,
Sam Mathai Chacko (GL)
On Thu, Oct 6, 2011 at 10:57 AM, Sam Mathai Chacko <samde...@gmail.com>wrote:
> Range("A3:A6").TextToColumns Range("A3"), 0, 1, False, True, False, False,
> False, False, Array(1, 4), True
>
> Regards,
>
> Sam
>
>
> On Thu, Oct 6, 2011 at 10:56 AM, B.N.Chethan kumar <
> chetankumar1...@gmail.com> wrote:
>
>> Hi,
>>
>> As the data volume is high... i will have 100 to 200 sheets in a
>> month..hence take more time....
>>
>> Can we write any macro to convert for all sheet in the work book.
>>
>> Thanks Asa Rossoff...i will try below option.
>>
>> Regards
>> Chethan Kumar BN
>>
>> On Thu, Oct 6, 2011 at 10:48 AM, Asa Rossoff <a...@lovetour.info> wrote:
>>
>>> Hello B.N.Chethan,****
>>>
>>> ** **
>>>
>>> Here are two methods courtesy of
>>> http://www.ozgrid.com/Excel/convert-true-time-date.htm****
>>>
>>> ** **
>>>
>>> 1. Option 1: Copy an empty cell, then highlight the cells (or entire
>>> column(s)) containing your textual date/times, then Paste Special>Add. The
>>> add operation forces Excel to convert the date/time.****
>>>
>>> ** **
>>>
>>> 2. Option 2: use a formula in another cell that does a neutral math
>>> operation like *=A3+0* or *=A3*1* or *=--A3*. You could if desired
>>> copy those cells to another location using Paste Special>Values.****
>>>
>>> ** **
>>>
>>> I'll just add that if the data is being imported from a text file, make
>>> sure you highlight the column containing date/times during import and
>>> specify the correct date format for that column; it may avoid the whole
>>> problem.****
>>>
>>> ** **
>>>
>>> Asa****
>>>
>>> ** **
>>>
>>> *From:* excel-macros@googlegroups.com [mailto:
>>> excel-macros@googlegroups.com] *On Behalf Of *B.N.Chethan kumar
>>> *Sent:* Wednesday, October 05, 2011 9:50 PM
>>> *To:* excel-macros@googlegroups.com
>>> *Subject:* $$Excel-Macros$$ Need a help -----very urgent****
>>>
>>> ** **
>>>
>>> Hi All,****
>>>
>>> ****
>>>
>>> I have attached file where i get some data in text format. I need convert
>>> to date and time format.****
>>>
>>> ****
>>>
>>> Currently using text to coloumn method which is manually pocess. As
>>> sheet count is high in a workbook, these is very painful work to do in month
>>> end.****
>>>
>>> ****
>>>
>>> Kindly request your help to very painful work.
>>>
>>> -- ****
>>>
>>> Regards
>>> B.N Chetan kumar****
>>>
>>> --
>>>
>>> ----------------------------------------------------------------------------------
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel****
>>>
>>> --
>>>
>>> ----------------------------------------------------------------------------------
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel
>>>
>>
>>
>>
>> --
>> B.N Chetan kumar
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> Sam Mathai Chacko
>
--
Sam Mathai Chacko
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
