2

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?

Sidney Levy
  • 155
  • 2
  • 9

2 Answers2

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.

Sidney Levy
  • 155
  • 2
  • 9
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.

Vinny Roe
  • 903
  • 4
  • 8
  • I'm not using VBA. I create a flat .csv file with comma delimited columns and then browse it in IE. But even if I try to type in this cell 07:1155 Excel converts it to 1.09375 – Sidney Levy Apr 03 '13 at 07:18