Instead of simply opening the file, use the Data>Text from File method to import the data. For column 4, specify 'Text' during the import. For columns 1-3, you should be able to get the formats you require by applying custom cell formats after importing the data.



For column 1, use: 00000
For column 2, use: DD/MM/YYYY HH:mm:ss or MM/DD/YYYY HH:mm:ss
For column 3, use: 00000-0000, though I do know what you mean by 'ignore 2-digit blank spaces'

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Paul:

Your suggested method for importing data does seem to be a little easier than what I was previously doing...opening my downloaded csv data file into a spreadsheet and then copying it over to where I desire the data in my main spreadsheet.

When I used your method, I formatted the columns to restore the dropping of preceding zeros so that annoying problem was easily resolved.

However, I am still getting a rather odd import data error. Here are two examples of numeric data from column four (20 digits): "00310906021959200001" & "00310906021959200016". Note the only difference is the last two digits in the string. After importing (by my earlier method or yours) from the csv text file, I always get "00310906021959200000" (last two digits are getting changed) for EVERY ROW. What is happening? When I format the column to text I get "3.10906E+17". What is going on during the import of the fourth column data? What are values getting changed and why can I not format the data as text? I am attaching a small sample csv data file if you or anyone else wants to play with it.

Many thanks!

Keith

Attached Files

Sample Data File.txt (1.1 KB, 8 views)