最新

Excel 怎么把文本型数字批量转成数值

数字左上角带绿三角,求和是零,排序也乱套,这是存成了文本。这篇讲四种转换办法各适合什么场景、几处容易丢数据的坑,以及怎么确认转对了。

从系统导出的表格,数字经常是文本格式:单元格左上角一个绿三角,求和结果是零,按大小排序也不对。逐个双击回车能改,几百行就不现实了。这篇讲几种批量转换的办法各适合什么场景、几处容易把数据弄丢的坑,以及怎么确认真的转对了。

入口在哪

两个判断位置先看清:一是单元格左上角有没有绿三角,有就是文本型数字;二是选中这列看状态栏,如果只显示计数不显示求和,也说明是文本。

转换的入口有几个:数据选项卡里的分列、开始选项卡里的选择性粘贴、以及直接写公式。三条路结果一样,适用场景不同。

具体步骤

  1. 先备份一份原表。转换过程中一旦丢精度或丢前导零,原数据就找不回来了。
  2. 分列法:选中整列,点数据选项卡的分列,直接点完成,不做任何分隔设置。这一步会强制重新识别类型。
  3. 选择性粘贴法:找个空白单元格输入数字 1 并复制,选中目标列,选择性粘贴里选乘。文本乘一会被强制转成数值。
  4. 公式法:在旁边的空列写转换函数,把原单元格包进去,回车后往下拖,再把结果复制回去、粘成值。
  5. 转换完成后检查数字格式,需要两位小数的设成数值并指定小数位,别让它显示成一大串。
  6. 删掉辅助列,保存。

几个坑

  • 前导零被吃掉。编号、邮编这类以零开头的,转成数值后零消失,必须先设成文本格式或自定义格式。
  • 超过十五位的长数字丢精度。身份证号、订单号末尾会变成零,这类字段根本不该转数值。
  • 公式转换后没粘成值。辅助列删掉,结果跟着变成错误值。
  • 把错误值一起转换。原列里有错误值或空行,转换后会生成一片错误。
  • 只转了一部分。选区漏掉末尾几行,求和结果还是不对。
  • 转换后没刷新透视表。基于旧数据做的透视表还按文本统计,数字对不上。

怎么验证

选中整列看状态栏,应该出现求和、平均值这些统计项,只有计数就说明还有文本残留。再用排序试一次,数值应该按大小正确排列,不是按字符顺序。最后挑几行手动核对,特别是带前导零和长数字的那几行。

常见问题

为什么求和结果是零

文本型数字不参与求和。转换后立刻能算出结果,如果还是零,检查是不是整列都带空格或不可见字符。

转换后小数点变了怎么办

可能原数据用的是逗号做小数点,或者是千分位混进了数值。先用查找替换清掉分隔符再转换。

能不能一次性转整张表

可以,选中整个区域做分列即可。但含前导零和超长编号的列要先排除,否则会被改坏。

一句话总结:文本转数值有四条路,分列最快、乘一最稳、公式最灵活,动手前先备份、先排除编号类字段,转完用状态栏和排序两项验证。