Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of 'Text to Columns' within VBA
As a result of a problem that I've got analysing data within a pivot-
table I need to remove the time stamp on the end of data that I've imported from a flat file (csv). Column B contains data as per below example (Title row 1 containing Created Date): Created Date 02/01/2007 08:10 02/01/2007 13:58 02/03/2007 10:58 02/03/2007 11:11 02/01/2007 08:41 06/03/2007 13:46 18/01/2007 15:35 I've managed to record the functionality to do what I want, however when I try and use this back it causes dates to reverse (e.g. my previous August dates later on in the data have instead of being 03/08/2007 have become 08/03/2007 - american format). I've tried ensuring the format of this data was correct pre-macro and then afterwards but to no avail. Please can someone help. Below is the recorded code: Range("B2").Select Range(Selection, Selection.End(xlDown)).Select Selection.texttocolumns Destination:=Range("B2"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 4), Array(10, 9)) Regards, Al. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of 'Text to Columns' within VBA
Change the Array to
FieldInfo:=Array(Array(0, 4), Array(10, 1)). Michael Arch. Please rate this posting if helpful " wrote: As a result of a problem that I've got analysing data within a pivot- table I need to remove the time stamp on the end of data that I've imported from a flat file (csv). Column B contains data as per below example (Title row 1 containing Created Date): Created Date 02/01/2007 08:10 02/01/2007 13:58 02/03/2007 10:58 02/03/2007 11:11 02/01/2007 08:41 06/03/2007 13:46 18/01/2007 15:35 I've managed to record the functionality to do what I want, however when I try and use this back it causes dates to reverse (e.g. my previous August dates later on in the data have instead of being 03/08/2007 have become 08/03/2007 - american format). I've tried ensuring the format of this data was correct pre-macro and then afterwards but to no avail. Please can someone help. Below is the recorded code: Range("B2").Select Range(Selection, Selection.End(xlDown)).Select Selection.texttocolumns Destination:=Range("B2"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 4), Array(10, 9)) Regards, Al. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of 'Text to Columns' within VBA
Also you can add this:
Selection.NumberFormat = "dd/mm/yyyy" " wrote: As a result of a problem that I've got analysing data within a pivot- table I need to remove the time stamp on the end of data that I've imported from a flat file (csv). Column B contains data as per below example (Title row 1 containing Created Date): Created Date 02/01/2007 08:10 02/01/2007 13:58 02/03/2007 10:58 02/03/2007 11:11 02/01/2007 08:41 06/03/2007 13:46 18/01/2007 15:35 I've managed to record the functionality to do what I want, however when I try and use this back it causes dates to reverse (e.g. my previous August dates later on in the data have instead of being 03/08/2007 have become 08/03/2007 - american format). I've tried ensuring the format of this data was correct pre-macro and then afterwards but to no avail. Please can someone help. Below is the recorded code: Range("B2").Select Range(Selection, Selection.End(xlDown)).Select Selection.texttocolumns Destination:=Range("B2"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 4), Array(10, 9)) Regards, Al. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of 'Text to Columns' within VBA
Hi Michael,
Thanks for this. Only prob' is that this seems to want to use the separated data as the next column now (e.g. the time). Is it possible to prevent it from this part? Otherwise this is fine, thank you. Could you also possibly explain the array part as well from a logic point of view so I understand for future ref? Thanks again, Al. On 3 Aug, 15:28, Michael wrote: Change the Array to FieldInfo:=Array(Array(0, 4), Array(10, 1)). Michael Arch. Please rate this posting if helpful " wrote: As a result of a problem that I've got analysing data within a pivot- table I need to remove the time stamp on the end of data that I've imported from a flat file (csv). Column B contains data as per below example (Title row 1 containing Created Date): Created Date 02/01/2007 08:10 02/01/2007 13:58 02/03/2007 10:58 02/03/2007 11:11 02/01/2007 08:41 06/03/2007 13:46 18/01/2007 15:35 I've managed to record the functionality to do what I want, however when I try and use this back it causes dates to reverse (e.g. my previous August dates later on in the data have instead of being 03/08/2007 have become 08/03/2007 - american format). I've tried ensuring the format of this data was correct pre-macro and then afterwards but to no avail. Please can someone help. Below is the recorded code: Range("B2").Select Range(Selection, Selection.End(xlDown)).Select Selection.texttocolumns Destination:=Range("B2"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 4), Array(10, 9)) Regards, Al.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of 'Text to Columns' within VBA
When you are recording the macro, and do text to columns and select fixed
width, on the next step you will see a formatting section on the right hand side of the window box that reads Column Data Format, select Date and then on the dropdown box select DMY. You may still need to add the following statement: Selection.NumberFormat = "dd/mm/yyyy" To make sure it will not change your data formatting. Regards, Michael Arch. " wrote: Hi Michael, Thanks for this. Only prob' is that this seems to want to use the separated data as the next column now (e.g. the time). Is it possible to prevent it from this part? Otherwise this is fine, thank you. Could you also possibly explain the array part as well from a logic point of view so I understand for future ref? Thanks again, Al. On 3 Aug, 15:28, Michael wrote: Change the Array to FieldInfo:=Array(Array(0, 4), Array(10, 1)). Michael Arch. Please rate this posting if helpful " wrote: As a result of a problem that I've got analysing data within a pivot- table I need to remove the time stamp on the end of data that I've imported from a flat file (csv). Column B contains data as per below example (Title row 1 containing Created Date): Created Date 02/01/2007 08:10 02/01/2007 13:58 02/03/2007 10:58 02/03/2007 11:11 02/01/2007 08:41 06/03/2007 13:46 18/01/2007 15:35 I've managed to record the functionality to do what I want, however when I try and use this back it causes dates to reverse (e.g. my previous August dates later on in the data have instead of being 03/08/2007 have become 08/03/2007 - american format). I've tried ensuring the format of this data was correct pre-macro and then afterwards but to no avail. Please can someone help. Below is the recorded code: Range("B2").Select Range(Selection, Selection.End(xlDown)).Select Selection.texttocolumns Destination:=Range("B2"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 4), Array(10, 9)) Regards, Al.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining Text from 2 Columns into 1 then Deleting the 2 Columns | Excel Worksheet Functions | |||
help with sorting text in columns to match other columns | Excel Discussion (Misc queries) | |||
merge text from 2 columns into 1 then delete the old 2 columns | Excel Worksheet Functions | |||
Linking text columns with text and data columns | Excel Worksheet Functions | |||
extracting text from within a cell - 'text to rows@ equivalent of 'text to columns' | Excel Programming |