☰

将带有前导破折号的文本字符串转换为负数

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2016
将带有前导破折号的文本字符串转换为负数

我使用的在线经纪商的网站一定是世界上最不完善的！他们不提供“导出”功能，而是告诉用户他们只需从浏览器中呈现的表格中复制数据，然后将其粘贴到 Excel 中即可。（真的！）

令人惊讶的是，Excel（2016，适用于 Mac）可以被诱导将“正文本数字”转换为可以通过数值函数操作的实际数字（SUM通常）。然而，我发现“负文本数字”无法转换为负数。

“正文本数字”为：123.45 美元
“负文本数字”为：-$543.21 （请注意前导文本字符hyphen）

编辑：
“负文本数”是：- 299.55 美元（发现主角是不是一个hyphen；它可能是一个em dash
我很抱歉造成混淆...我现在想知道什么样的程序员可能会在充满数字的网页中使用em dash而不是，但这是我的问题minus sign/hyphen网上经纪人。

我可以按如下方式转换单元格 A1 中的“正文本数字”：=VALUE(RIGHT(A1,6))

但这不适用于“负文本数字”。它得到了正确的数字，但丢失了负号。我尝试更改该chars值，但这没有帮助。

我也尝试了以下方法，但没有成功：
1.乘以 1
2.文本到列
3.格式化单元格...

除了手动重新输入之外，还有其他方法可以做到这一点吗？

答案1

我认为我找到了一个简单的解决方案。在以破折号开头的单元格中，突出显示并复制破折号。使用 Excel 的“查找和替换”功能，将破折号粘贴到“查找内容”块中，然后在“替换为”块中输入减号。将替换应用于要转换的所有单元格。

答案2

如果单元格 A1 包含文本格式为“$123.45”或“-$543.21”，您可以使用以下公式获取数值。

=IF(LEFT(A1,1)="-",0-VALUE(RIGHT(A1,LEN(A1)-1)),VALUE(A1))

如果文本以“-”开头，这将获取“-”后的所有内容的值并使其为负数。

答案3

我建议：

  1. 提取符号后的值$，不使用固定位置
  2. 通过检查符号前的符号来检查值的符号$。

因此公式可能是

=IF(IF(ISERROR(FIND(MID(A1,FIND("$",A1)-1,1),"<dash><hyphen>")),3,FIND(MID(A1,FIND("$",A1)-1,1),"<dash><hyphen>"))<3,-MID(A1,FIND("$",A1)+1,LEN(A1)),0+MID(A1,FIND("$",A1)+1,LEN(A1)))

在哪里

"<dash><hyphen>"- 可能带有负号的文字（以任意顺序用适当的字符替换占位符，以便在所示情况下获得 2 个符号的文字）

3- 大于前一个文字的字符数的数字

Z1这些值可以被对包含所有可能的“减号”（分别如和）的单元格的引用所替换1+LENGTH(Z1)。

更新。正在调试公式。

在空白工作表上创建一个表格。

向 A 列插入 4 个不同的值（带/不带前导空格、正数/负数）。

插入以下公式：

Cell          Formula                      Meaning
-------------------------------------------------------------
$B$1          =IF(I1,-G1,0+G1)             Result
$C$1          =LEN(A1)                     Data length in source
$D$1          =FIND("$",A1)                $ position in source
$E$1          =MID(A1,D1-1,1)              symbol before $
$F$1          =FIND(E1,"<dash><hyphen>")   symbol before $ position in hyphens list
$G$1          =MID(A1,D1+1,C1)             number from source
$H$1          =ISERROR(F1)                 correction when no symbol before $ in list
$I$1          =IF(H1,3,F1)<3               define is a source positive or negative

将此公式包复制到第 2-4 行。检查是否获得如下输出：

     A        B      C   D   E      F       G    H       I
1    $123     123    4   1   #VAL!  #VAL!   123  TRUE    FALSE   
2    -$234    -234   5   2   -      1       234  FALSE   TRUE    
3      $345   345    6   3          #VAL!   345  TRUE    FALSE   
4      -$456  -456   7   4   -      1       456  FALSE   TRUE

CI列包含单个计算步骤，B列包含所有替换的最终公式。

计算失败在哪里？

相关内容