Hi there,

I have recently been provided with an Excel spreadsheet that is made up of 81 combined CSV files. The person who created it unfortunatly didn't import the data, they copied and pasted it. This has meant that Excel has tried to apply intelligent formatting to some of the fields, but it has done it incorrectly.

The field names are codes for IT equipment and have names like "05:07" and "00:2A". The cells that have letters in are fine as they are as they have been kept as text fields, however the "05:07" has a custom format applied (hh:mm), and in the formula bar it is displayed as "05:07:00" which is not what we want. When I change the format to 'General' Excel converts the 'time' to decimal, eg "0.213194444444444".

When I manually enter a ' in front of the 05:07 the problem is nearly resolved as it changes to 'general' without turning to decimal, but displays as 05:07:00. The ='&C2 formula converts it to decimal before adding the '. I have over 1,000 fields that this problem occurs in, and after a day of trying to work out what the problem is I'm stuck!

Any clues? Is there a way to stop Excel converting the 'time' data into decimal?

Jenny