☰

将带有“带空白的文本数字”的单元格转换为可以计算的数字

tag-icon • tag-icon microsoft-excel microsoft-excel-2016
将带有“带空白的文本数字”的单元格转换为可以计算的数字

我是 Excel 初学者，花了一些时间来解决这个问题，但最终放弃了。如果有人能给我答案，我将不胜感激。

我有一个数据集，其中包含看起来像数字的单元格，但这些“数字”不允许进行任何计算。我在 Google 上搜索后发现，这些看起来像数字的单元格实际上可能是“文本”（我猜这是某些编程语言中的某种字符串格式）。

然后我找到了一些方法将看起来像数字的文本转换为真正的数字。包括“文本到列”方法---失败了，“选择性粘贴方法”---失败了。

看起来这是两个问题的结合。一个是这些单元格实际上是文本，另一个是这些单元格中的数字以空格结尾。在解决这个问题之前，请允许我再描述一下我为解决这个问题所做的努力。我还发现内置函数 VALUE() 可用于将文本转换为其数值。我试过了，但失败了。然后我意识到这些看起来像数字的文本末尾有一个空格！所以，我使用内置函数 TRIM() 为每个看起来像数字的单元格获取一个没有结尾空格的新选项卡。

但是，当我在新选项卡中使用 =VALUE(B3) 时（其中已经使用 TRIM() 函数删除所有空白），仍然出现错误。你们能解释一下为什么我的尝试失败了，以及为什么我通过谷歌搜索找到的方法也失败了（“选择性粘贴”和“文本到列”）？

我甚至尝试关注这个帖子，但也失败了...... https://stackoverflow.com/questions/2741991/how-to-convert-text-to-numbers 任何帮助都将不胜感激，谢谢！

答案1

一个常见问题是 NBSP 字符，特别是当文本来源是 HTML 文档（网页）时。可以通过以下方法删除它：

  =substitute(b3,char(160),"")

答案2

更通用的解决方案可能是提取所有数字字符，并让 Excel 从一开始就将结果视为数字。以下公式仅提取“0-9”、“-”和“。”，尽管如何添加或删除其他字符非常明显：

=VALUE(TEXTJOIN("",TRUE,IFERROR(UNICHAR(((UNICODE(MID(A1,SEQUENCE(1,LEN(A1)),1))<>47)*(UNICODE(MID(A1,SEQUENCE(1,LEN(A1)),1))>44)*(UNICODE(MID(A1,SEQUENCE(1,LEN(A1)),1))<58)*1)*UNICODE(MID(A1,SEQUENCE(1,LEN(A1)),1))),"")))

因此，无论数据源使用哪种难以列出的空格和“不间断”空格或任何其他字符来格式化其数据，甚至可能曾经使您更难将其数据提取成某种有用的形式，无论怎样，只有数字“0-9”、负号和小数点被提取到带有公式的单元格中（或提取到直接使用输出的某些公式中），所以无论它们是什么，它们都不会进一步打扰您。

有六种“常用方法”可以解决这个问题，显然上面的方法对发帖者来说效果很好，但根据我的经验，我知道至少有十几个字符被使用过，尽管因为我没有保留列表，所以每次使用零碎的方法都会有新的斗争。此外，这可以让你摆脱F2-Edit-Enter和Paste|Special|Multiply (by 1)不起作用的情况……它们应该起作用，但显然 Excel 有时会有一些用户无法编辑的隐藏格式字符。没关系，那些也会被遗弃。

相关内容