Hello,
I’m trying to convert some very badly formated xls files to readable ones.
For the purpose I used the Text to Column feature in excel itself. As there a lot of files to be processed I recorded a macro and tried to apply it to the rest of the files.
But there is something wrong as it doesn’t convert the dates properly.
<o
> </o>
The dates to be converted are in the following format: yyyymmdd e.g. 20100706
<o> </o>
<o> </o>
Here is the macro:
<o> </o>
thanks
I’m trying to convert some very badly formated xls files to readable ones.
For the purpose I used the Text to Column feature in excel itself. As there a lot of files to be processed I recorded a macro and tried to apply it to the rest of the files.
But there is something wrong as it doesn’t convert the dates properly.
<o
> </o>The dates to be converted are in the following format: yyyymmdd e.g. 20100706
<o
> </o><o
> </o>Here is the macro:
Code:
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
4), Array(6, 4), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _
Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array( _
25, 1), Array(26, 1), Array(27, 1)), TrailingMinusNumbers:=Truethanks
Last edited:
