How to prevent Excel from handling strings contain

Question

I am generating csv files, and some cells have the format nn:nnnn , i.e. digits separated by a colon. It's not a time format nor a date format, it's just text cells and I don't want them to be re-formatted at all.

I've added some logic to my code in order to identify if it looks like a legal time format or a date, and if so, I wrap that string like this ="nn:nnnn". But I'm not interested in adding those characters to all the cells.

It almost solved my problem, but there are still some cases such as 07:1155 that MS Excel insists to translate it as 1.09375. Other cells such as 68:0062remain intact. Is there a way to recognize what strings are going to be calculated or translated?

Is there any workaround such as any set-up in MS Excel to tell it not to perform any translation on these kind of text?

Answer 1

Instead of just opening your CSV in Excel, you might try doing (Menus) Data/Get External Data/From Text. Or if you're using VBA, that would be something like:

With ActiveSheet.QueryTables.Add(Connection:="TEXT;H:\csvtest.csv" _
    , Destination:=Range("$A$3"))
    .Name = "csvtest_1"
    .FieldNames = True
   etc
End With

You may need to specify Text as the incoming column format.

Answer 2

I've got the following answer from Mr. JP Ronse at the Microsoft Community forum

Try to precede a string like 07:1155 with a single quote. A single quote prevents Excel from interpreting the value.

For some reason Excel interpret a string like 07:1155 as a time and translates it to the value. Excel sees 07:1155 as 7 hours and 1155 minutes, translated to values:

07:00 => 0.291666666666667 1155 minutes => (1155/60)/24 => 0.802083333333333

The sum is 1.09375

It looks as there is no translation on values like n:00nn or like n:0nnnnn

Checking on the 2 n's after the colon (not 00) could be a workaround.

Answer 3

I generated a CSV file containing the values you cite and imported it into excel; all values were imported as "general" - none of them were converted to any other format. I cannot reproduce the behavior you describe.

Had I been able to reproduce the behavior, I had planned to try wrapping the imported values in =Text(Value,"#")