哎呀，说起这个，我的头就隐隐作痛。不是因为它难，而是因为太常见了，常见到让人烦躁。有多少次，你辛辛苦苦从某个系统里导出一堆数据，或者客户甩给你一份报表，打开一看，好家伙，那一列本该是数字的东西，偏偏就在每个单元格的左上角带着个不起眼的绿色三角号。鼠标一晃过去，弹出来的提示赫然写着：“此单元格中的数字为文本格式，或其前面有撇号。” 或者更直接：“将文本转换为数字”。
这简直是数据处理里的“拦路虎”！看着像是数字，3.14啊，1000啊，-50啊，啥都有。可你就是不能直接拿它们去做加减乘除，不能求和，不能平均，排序也可能乱套。它们披着数字的外衣，骨子里却是冰冷的文本。就像你想跟一个人握手，伸出手才发现对方是个蜡像，看着像，但没反应。
那怎么办？总不能对着几千几万行数据一个个去双击单元格再按回车吧？（虽然说，对于只有寥寥几个异常值的，这确实是最直接、最不需要动脑筋的方法，点一下那个错误提示旁边的小下拉箭头，选“转换为数字”也行，但效率嘛……基本等于龟速。）
所以，我们得想点高效率的法子。跟这些调皮的文本格式数字死磕到底！
第一种方法：查找替换大法，专治“脏”数据。
有时候那些数字之所以变文本，是因为里面藏了点不该有的东西。最常见的就是空格，或者那些从网页、PDF里复制过来带有的非打印字符。甚至可能是货币符号，$啊，€啊，¥啊这些。软件一看，嚯，里面有奇奇怪怪的字符，那我把你当文本总没错吧？
这时候，查找替换就派上大用场了。选中你那一列“伪数字”，按下Ctrl+H（或者在菜单里找“查找和选择” -> “替换”）。
- 清理空格： 在“查找内容”里敲一个空格，在“替换为”里什么都不填。点“全部替换”。能解决不少问题。有时候是全角空格，也得替换掉。
- 清理特定符号： 如果你知道是因为有货币符号导致的，比如$100变成文本了，那就在“查找内容”里输“$”，“替换为”里留空，替换掉。逗号也经常是罪魁祸祸，比如“1,000”在某些设置下会被当文本，那就查找“,”替换为空。
- 清理看不见的字符： 这个稍微高级点。有时候是换行符之类的。在Excel里，你可以在“查找内容”里按住Alt键然后输入数字小键盘上的010（这是换行符的ASCII码，对于某些导入格式有用），或者
Ctrl+J（这个也表示换行符）。替换为空。这个需要一点经验，不是万能的，但有时候就靠它救命。
查找替换的好处是直接修改了单元格的内容，把“脏东西”去掉了，剩下的纯数字字符串往往就会被Excel自动识别成数字格式了。就算没自动识别，后面再用其他方法就更容易成功。
第二种方法：分列大法，有时一招制胜。
这个方法有点奇妙，它本意是用来把一列数据按分隔符（比如逗号、Tab键）或固定宽度分成多列的。但它有一个副作用，就是在处理过程中，如果某一列看起来像数字，它会尝试将其转换成数字格式。
怎么用呢？选中那一列问题数据，去数据选项卡里找到“分列”。弹出来的向导，你可以直接点“下一步”，然后“下一步”。在第三步，也就是“列数据格式”那里，保持默认的“常规”就行（或者你也可以尝试选“文本”再改回“常规”，有时候有奇效，但我一般就保持默认）。然后点“完成”。
很多时候，仅仅是过一遍分列的流程，Excel就被“提醒”了：“哦，原来你这些东西是想让我当数字看啊！” 然后，唰一下，所有的绿色三角号就消失了，数据乖乖变成了数字格式，右对齐（默认情况下数字是右对齐的，文本是左对齐的，这也是一个判断的小技巧）。这个方法特别简单粗暴有效，我经常在数据不太复杂的时候优先尝试它。
第三种方法：函数大法，灵活精确。
如果上面两种方法搞不定，或者你需要更精细的控制（比如只转换符合某种条件的，或者在转换前进行一些预处理），那就得上函数了。
最核心的函数是VALUE函数。它的作用很简单直接：把一个看起来像数字的文本字符串，转换成真正的数字。
比如，你的文本数字在A1单元格，你在旁边的B1单元格输入 =VALUE(A1)，如果A1里的文本能被识别成数字，B1就会显示相应的数字格式结果。然后你把B1这一列公式往下拉填充，再把B列的数据“选择性粘贴”为“值”回到A列（或者一个新地方），覆盖掉原来的文本。
但是，VALUE函数有个缺点，如果A1里的文本根本就不是合法的数字形式（比如“abc”或者“12-34-56”这种日期文本），VALUE函数就会报错，显示#VALUE!。这可不行，我们不能因为一两个“坏蛋”就让整列数据都出错。
这时候，我们需要结合其他函数来增强VALUE的能力。
- 处理错误： 用IFERROR函数。
=IFERROR(VALUE(A1), A1)。这句代码的意思是：尝试用VALUE转换A1，如果成功了，就显示转换后的数字；如果失败（出现错误），就保留A1原来的内容。这样至少不会丢失数据。你也可以在失败的时候让它显示0或者其他值，=IFERROR(VALUE(A1), 0)。 - 预清理再转换： 如果你知道文本里有逗号、$符号等等干扰项，可以在VALUE外面套上SUBSTITUTE函数先做个“清洁”。比如，要去掉美元符号和逗号再转数字：
=VALUE(SUBSTITUTE(SUBSTITUTE(A1,"$",""),",",""))。这里，先用第一个SUBSTITUTE把所有的“$”替换为空，再用第二个SUBSTITUTE把所有的“,”替换为空，得到一个干净的数字字符串，最后用VALUE去转换它。这招很强大，可以层层嵌套SUBSTITUTE来去掉各种乱七八糟的字符。 - 更简单的强制转换： 有时候，仅仅对文本数字执行一个简单的数学运算，也能强制Excel将其视为数字。最常用的是乘以1或者加上0。
=A1*1或者=A1+0。原理是数学运算只能对数字进行，Excel遇到文本但上下文需要数字时，会尝试自动转换。这个方法非常简洁，而且对于比较“干净”的文本数字（比如只是格式问题，里面没杂七杂八的符号），效果非常好。如果遇到非数字文本，它可能会报错，所以结合IFERROR会更稳健：=IFERROR(A1*1, A1)。
用函数的方法，你可以在不破坏原始数据列的情况下，在旁边的新列里生成干净的数字格式数据。然后复制那一列新数据，回到原位或者目标位置，使用“选择性粘贴”里面的“值”。这一步是关键！因为你只需要函数计算出来的最终结果（数字），而不是函数本身。粘贴为值后，那一列就真正变成了数字格式，不再依赖公式，绿色的三角号也无影无踪了。别忘了粘贴完值后，可以把原来的函数列删掉，保持表格整洁。
一些额外的提醒和思考：
- 自定义格式不是数据类型！ 很多人会混淆这一点。在单元格上右键，选择“设置单元格格式”，里面的“数字”分类和各种自定义格式，它们只决定了数字“长什么样”（显示几位小数、带不带千位分隔符、负数怎么显示等等）。它不改变单元格里存储的数据类型。你不能指望把一个文本格式的单元格设置为“数字”格式，它就自动变成数字了。这是两个层面的事儿。要先让它“是”数字，然后才能决定它“看起来像”什么样的数字。
- 丢失前导零： 如果你的“数字”实际上是类似产品编号、身份证号或者邮政编码这种，它们可能有前导零（比如007）。一旦你用上述方法成功转换为数字格式，这些前导零会自动丢失（因为数学上007就是7）。如果你的原始数据需要保留前导零（意味着它们本质上是标识符，而不是用来计算的纯数值），那么它们本来就不应该被转换成数字格式，而应该保留文本格式。转换前要想清楚数据的真实含义和用途。
- 隐藏的字符： 有时候最让人抓狂的是那些你眼睛看不到的非打印字符。它们可能来自网页复制，可能来自某些系统导出时的“毛病”。查找替换结合特殊的查找代码（比如前面说的Alt+010）或者先把数据粘贴到记事本里“洗”一遍（记事本通常只保留纯文本）再复制回来，有时是无奈但有效的办法。
- 组合拳： 很多时候，单一的方法不足以解决问题。你可能需要先用查找替换清理掉明显的脏东西，再用分列或者VALUE函数进行格式转换。就像打扫房间，先扫地（替换），再拖地（分列/函数）。
- 检查是王道： 不管用什么方法，转换完成后一定要抽查一些数据，尤其是开头、结尾和中间的样本。最好能对转换前后的数据列求个和或者计数，看看结果是否一致，以此验证转换是否成功且没有意外丢失数据。
处理这些文本格式的数字，就像是跟数据打交道日常要经历的“磨难”。每一次成功转换，都像驯服了一匹野马，让它乖乖听话。虽然过程可能有点反复，甚至偶尔让人抓狂，但掌握了这些方法，你就有了应对绝大多数情况的武器库。下次再看到那个绿色三角号，至少你知道，有的是法子把它干掉，而不是只能对着它干瞪眼。这份解决问题的掌控感，还是挺让人有成就感的。所以，别怕，挑个顺手的方法，开干吧！让你的数据真正“动”起来，能参与计算，能准确排序！
发表回复