Google Sheets中如何将IMPORTHTML返回的文本转换为数值

问题现状 #

• 对Z列执行排序操作失败，原因是IMPORTHTML公式返回结果为文本字符串，典型返回值示例为386.02B
• 当前在用的原始公式：

=IFERROR(SUBSTITUTE(INDEX(IMPORTHTML("https://finviz.com/quote.ashx?t="&$D3,"table",9),4,2),"*","",)

• 参考效果截图：
  

调整方案 #

抓取结果末尾带的字母是金融数值的单位缩写：K代表千、M代表百万、B代表十亿、T代表万亿，只需要在原有公式外层增加单位识别、数值换算逻辑，输出纯数值即可支持正常排序。
调整后可直接使用的公式：

=IFERROR(
  LET(
    raw_val, SUBSTITUTE(INDEX(IMPORTHTML("https://finviz.com/quote.ashx?t="&$D3,"table",9),4,2),"*",""),
    num, VALUE(REGEXEXTRACT(raw_val, "\d+\.?\d*")),
    unit_suffix, RIGHT(raw_val, 1),
    calc_multi, SWITCH(unit_suffix, "T", 10^12, "B", 10^9, "M", 10^6, "K", 10^3, 1),
    num * calc_multi
  )
)

• 公式执行逻辑：
  • 先运行原有抓取、去星号的逻辑，拿到原始文本结果
  • 用正则提取文本中的纯数字部分，通过VALUE函数转为数值类型
  • 提取文本末尾的单位字符，匹配对应的换算倍率
  • 最终输出换算后的纯数值，该结果既可以正常排序，也支持后续数值计算

小提示：如果粘贴公式后提示函数不存在，检查表格的区域设置，将区域调整为美国即可兼容所有函数名。

内容的提问来源于stack exchange，提问作者Amar Patel