Parsing text file with fixed width Date problem

[k4ghg]

I am reading a text file using VBA's string (i.e., Left, Right and Mid) functions. My problem is that when I encounter a string with starting and ending zeros they are truncated. For example 05192010, becomes 519201, or 00001.100 becomes 1.1 and i want the date and the entire field. Is there away to keep all the characters? Thanks... Ronnie

 

[ThichTraXanh]

because it reads that as numeric, if you format it as text, or custom format then it's ok.

If you use excel then when you import the text file, either you import with delimit or width, select the column that you don't want it change, SET IT TO TEXT.

 

[SkipVought]

Hi,

Trouble is that, 05192010 is NOT A REAL DATE!!!

Furthermore, the string structure (mmddyyyy) will not even colate properly!!!

Solution:

1. NOT VBA
2. IMPORT your text file using Data > Import External Data...
3. Parse the Date Column using the MDY option to CONVERT your string to a REAL DATE.

If this is a recurring task, turn on you macro recorder and record EDITING the Import process. Post back with your recorded code if you need assistance.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]