如何防止Excel将包含冒号的字符串作为公式处理 [英] How to prevent Excel from handling strings containing a colon as formulas

问题描述

我正在生成csv文件，一些单元格格式为 nn：nnnn ，即以冒号分隔的数字。它不是时间格式也不是日期格式，它只是文本单元格，我不想要它们重新格式化。

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.

我添加了一些逻辑到我的代码，以确定它看起来像一个法律时间格式或日期，如果是这样，我包装该字符串像这样 =nn：nnnn。但我不想把这些字符添加到所有的单元格。

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.

这几乎解决了我的问题，但仍然有一些情况，如 07：1155 ，MS Excel坚持将其转换为 1.09375 。其他单元格如 68：0062 保持不变。有没有办法识别什么字符串将被计算或翻译？

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?

有任何解决方法，如任何设置在MS Excel中，告诉它不执行任何翻译这些类型的文本？

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?

推荐答案

我有以下答案JP Ronse先生在 Microsoft社区论坛

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

尝试使用单引号将像07：1155之类的字符串放在前面。
单引号阻止Excel解释该值。

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

由于某种原因，Excel将一个字符串如07：1155解释为时间，并将其转换为该值。
Excel将07：1155显示为7小时1155分钟，并翻译为值：

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分钟=> 1155/60）/ 24 => 0.802083333333333

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

总和是1.09375

The sum is 1.09375

看起来没有翻译on n：00nn或like n：0nnnnn

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

检查冒号后面的2 n（不是00）可能是一种解决方法。

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

这篇关于如何防止Excel将包含冒号的字符串作为公式处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！