☰

将包含负数的文本转换为货币

tag-icon • tag-icon microsoft-excel worksheet-function
将包含负数的文本转换为货币

我有一个从 PDF 转换而来的大型电子表格。其中一列包含代表美元价值的数字。负数以尾随破折号（或连字符）显示，例如 123.45-

我需要将此列转换为可用的数字格式。此公式=IF(ISNUMBER(SEARCH("-",X3)),-VALUE(SUBSTITUTE(X3,"-","")),X3)在某些单元格上有效，但#VALUE!在其他单元格上会产生错误。有什么改进建议吗？

答案1

Lambda 公式

=BYROW(X3:X1000,
   LAMBDA(r, 
     IFERROR(IF(FIND("-",r,LEN(r)-2)>0,
               ("-"&SUBSTITUTE(SUBSTITUTE(r,"-","")," ",""))/1,),
             r)))

单排公式

=IFERROR(IF(FIND("-",X3,LEN(X3)-2)>0,
           ("-"&SUBSTITUTE(SUBSTITUTE(X3,"-","")," ",""))/1,),
         X3)

所有价值都是“数字”

如果您检查的列中的所有内容都应该是正数或负数，那么以下修改后的代码会更好。

与 LAMBDA 公式的不同之处在于，FIND删除了函数字符限制，SUBSTITUTE添加了一个函数来从正值中去除空格，并且正字符串也根据需要强制转换为数字。

=BYROW(X3:X1000,
    LAMBDA(r,
       IFERROR(IF(FIND("-",r)>0,
              ("-"&SUBSTITUTE(SUBSTITUTE(r,"-","")," ",""))/1,),
       SUBSTITUTE(r," ","")/1)))

 

代码注释

1   =IFERROR(IF(FIND("-",r,LEN(r)-2)>0,
2              ("-"&SUBSTITUTE(SUBSTITUTE(r,"-","")," ",""))/1,),
3            r)
线 评论
  1 FIND将搜索限制为最后两个字符，LEN(r)-2以避免误报，同时仍能克服尾随空格。鉴于您对所涉及数据的了解，您可以放宽该限制（例如）或完全删除您认为合适的LEN(r)-4限制。公式被包装起来以处理未找到时的任何错误并返回原始值（第 3 行）。FIND("-",r)

IFERROR-r
  2 使用嵌套函数删除旧值中的所有-空格，然后在其前面添加一个。然后对结果字符串进行数学计算，将其强制转换为数字。 在本例中使用了。SUBSTITUTE-

/1
  3 -如果未找到则仅传回值（IFFERROR在第 1 行）

在此处输入图片描述

相关内容