Len函数无法返回datetime格式单元格准确字符数及Find函数报错求助

问题根源 #

Excel里显示为yyyy-mm-dd hh-mm-ss的单元格，本质存储的是数值（整数部分是从1900年1月1日开始的天数，小数部分是时分秒占一天的比例），并非文本字符串。直接用Len、Find这类文本函数时，它们读取的是这个数值的字符数（比如日期对应的数值可能是类似45230.56789这样的数字，字符数约15），而非你看到的格式化文本，这就是Len返回15、Find报错的核心原因。

解决方法 #

• 方法1：用TEXT函数转成文本后操作
  先通过TEXT函数把datetime数值转换成指定格式的文本字符串，再调用文本函数：

  • 计算字符数：=LEN(TEXT(A1,"yyyy-mm-dd hh-mm-ss"))，此时会返回正确的19
  • 查找特定字符：比如找第一个-的位置，用=FIND("-",TEXT(A1,"yyyy-mm-dd hh-mm-ss"))，不会再出现#VALUE!错误

• 方法2：永久转成文本格式
  先把单元格格式设置为「文本」，然后双击目标单元格并回车，或者用「数据」选项卡的「分列」工具（按默认步骤完成），就能把datetime数值转换成显示格式的文本，之后直接用Len、Find函数即可正常工作。

内容的提问来源于stack exchange，提问作者phemmy 05