Excel VBA中NumberFormat无法改变单元格值的问题求解

问题根源分析 #

你遇到的核心问题是：当给原本是文本格式的“2 10”设置数字格式时，Excel会自动忽略空格把文本转成数字210——此时单元格显示的是格式化后的0210，但实际存储的是纯数字210，自然就没办法按原始的空格拆分逻辑来解析日期了。

我整理了两种场景下的解决方案，你可以根据自己的数据状态选择：

情况1：单元格还保留原始文本“2 10”（未被转为数字） #

如果你的数据还没被Excel自动转换，先右键单元格→设置单元格格式→选择「文本」锁定原始内容，再用下面的方法转日期：

方法1：公式拆分转换（推荐） #

因为“2 10”代表10月2日（日+月的结构），我们可以拆分空格前后的内容再组合成标准日期：

• 如果你用的是Excel 365/2021，直接用TEXTSPLIT拆分：

  =DATE(YEAR(TODAY()), TEXTSPLIT(A1, " ")[2], TEXTSPLIT(A1, " ")[1])
  

  这个公式会把空格后的“10”作为月份，空格前的“2”作为日期，年份默认用当前年，最终生成可识别的日期值。

• 兼容旧版Excel的公式：

  =DATE(YEAR(TODAY()), RIGHT(A1, LEN(A1)-FIND(" ", A1)), LEFT(A1, FIND(" ", A1)-1))
  

  原理是先找到空格的位置，拆分出日和月，再组合成日期。

方法2：Power Query批量处理（适合大量数据） #

1. 选中A列数据→点击「数据」选项卡→「从表格/区域」（记得勾选“我的表格有标题”）
2. 在Power Query编辑器中，选中目标列→「转换」选项卡→「拆分列」→「按分隔符」→选择空格，拆分为两列
3. 添加自定义列，输入公式组合日期：

   = Date.FromText([Column1] & "/" & [Column2] & "/" & Date.Year(DateTime.LocalNow()))
   

   （这里是按“日/月/年”组合，你可以根据需要调整顺序）
4. 关闭并上载到Excel，就能得到标准的日期列了。

情况2：单元格已经变成数字210（显示为0210） #

如果数据已经被转成数字210，我们可以通过数字结构拆分出日和月：

方法1：直接拆分数字 #

因为原始是“日（1位）+月（2位）”的组合，所以取数字最后两位作为月份，前面的作为日期：

=DATE(YEAR(TODAY()), RIGHT(A1,2), LEFT(A1, LEN(A1)-2))

比如数字210，RIGHT(A1,2)得到10（月份），LEFT(A1,1)得到2（日期），直接组合成10月2日的日期。

方法2：先还原文本再处理（兼容多位数日期） #

如果有的数据是“10 12”转成1012（代表12月10日），可以先把数字转成固定长度的文本再拆分：

=DATE(YEAR(TODAY()), RIGHT(TEXT(A1,"000"),2), LEFT(TEXT(A1,"000"), LEN(TEXT(A1,"000"))-2))

TEXT(A1,"000")会把数字统一转成3位文本，再拆分最后两位（月份）和前面的部分（日期）。

最后提醒 #

转换完成后，记得右键结果列→设置单元格格式→选择你需要的日期显示样式（比如yyyy/mm/dd）。如果你的原始格式是“月 日”（比如“2 10”代表2月10日），只需要把公式里的日和月位置调换就行。

内容的提问来源于stack exchange，提问作者Mehdi Flame