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?
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.
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.
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,"#")
