[求助]文本中提金额

aichong

QUOTE:

以下是引用xj4587在2008-9-3 20:07:20的发言：

暂时支持不到小数。金额不可写第一位，相当经典

改一下就支持小数了，支持小数2位则公式改为：

=SUMPRODUCT(--TEXT(MID(TEXT(MID(SUBSTITUTE(B2,"元",REPT(" ",15)),ROW($1:$99),15),),2,15),"0.00;;;!0"))

支持负数，则改为：

=SUMPRODUCT(--TEXT(MID(TEXT(MID(SUBSTITUTE(B2,"元",REPT(" ",15)),ROW($1:$99),15),),2,15),"0.00;-0.00;;!0"))

[此贴子已经被作者于2008-9-3 23:08:28编辑过]

fdd

QUOTE:

以下是引用xj4587在2008-9-3 20:07:20的发言：

暂时支持不到小数。金额不可写第一位，相当经典

这个问题是可以很容易解决的:

=SUM(--TEXT(MID(TEXT(MID(SUBSTITUTE("我"&B2,"元",REPT(" ",15)),ROW($1:$99),15),),2,15),"0.00;;;!0"))

[此贴子已经被作者于2008-9-3 23:13:07编辑过]

aichong

宏表函数的解法：

定义名称：zz=EVALUATE("{"""&SUBSTITUTE(SUBSTITUTE($b2,"元",""";"""),"罚",""";""")&"""}")

单元格公式（支持2位小数及负数）：

=SUM(IF(ISNUMBER(--zz),--zz)) ——数组公式

或： =SUMPRODUCT(--TEXT(zz,"0.00;-0.00;;!0"))

linpansheng

哪位版主能好好讲解这一道公式嘛？我觉得这公式用得很巧妙啊，但很难理解啊！山版主出来讲解一下！

山菊花

向x.f.zhao学习，写出这么好的公式。

公式中的15是为了适应15位长度的数字，为了解释的方便，现将它改小一点，本问题中，假如罚金不超过千元，含小数最大为6位数：

B2：18日未关挡车门处罚30.05元，20日爬车处罚123元，

C2=SUMPRODUCT(--TEXT(MID(TEXT(MID(SUBSTITUTE(B2,"元",REPT(" ",5)),ROW($1:$40),7),),2,6),"0.00;;;!0"))

1、把“元”替换成足够长度的空格，使后续步骤中能够从文本中截取出纯数字字符串。

在编辑栏中，按如下所示选取文本，按F9，可得到处理后的结果：

=SUMPRODUCT(--TEXT(MID(TEXT(MID(SUBSTITUTE(B2,"元",REPT(" ",5)),ROW($1:$40),7),),2,6),"0.00;;;!0"))

结果为："18日未关挡车门处罚30.05     ，20日爬车处罚123     ，"

2、从文本的每一位开始，向后截取7位字符

B列中最长文本为29，替换空格后，不超过40，所以，公式中使用参数ROW($1:$40)。实际使用中，可设置一个足够大的数字，如99或200等。

MID(SUBSTITUTE(B2,"元",REPT(" ",5)),ROW($1:$40),7)得到的结果是：

"18日未关挡车";

"8日未关挡车门";

"日未关挡车门处";

…… ……

"处罚30.05";

"罚30.05 ";

"30.05  ";

"0.05   ";

".05    ";

"05     ";

"5     ，";

"     ，2";

"    ，20";

"   ，20日";

"  ，20日爬";

" ，20日爬车";

"，20日爬车处";

"20日爬车处罚";

…… ……

3、忽略数字，保留文本

这一步是本公式最为精彩的部分，我们一定要慢慢品味。

请跟我一起动手操作：

选择E1:E40，输入公式：

=MID(SUBSTITUTE(B2,"元",REPT(" ",5)),ROW($1:$40),7)

按Ctrl+Shift+Enter结束。

选择F1:F40，输入公式：

=TEXT(MID(SUBSTITUTE(B2,"元",REPT(" ",5)),ROW($1:$40),7),)

同样按Ctrl+Shift+Enter结束。

QUOTE:

公式中，省略了TEXT()的第二个参数：

=TEXT(MID(SUBSTITUTE(B2,"元",REPT(" ",5)),ROW($1:$40),7),";")

结果如下所示：

18日未关挡车	18日未关挡车
…… ……	…… ……
处罚30.05	处罚30.05
罚30.05	罚30.05
30.05
0.05
.05
05
5     ，	5     ，
，2	，2
…… ……	…… ……

为什么这样做？下一步才是豁然开朗。

这么好的办法，我是想不出来的。

4、让鱼浮出水面

注意观察一下，以上结果中，第二位开始是数字的，仅有两个：“罚30.05 ”和“罚123  ”，抓住了这两个数，几乎接近最后目标了。用Mid()将40个文本处理一下，从第2位开始，取6位。

可在工作表中按以上办法演示一遍。

G1:G40=MID(TEXT(MID(SUBSTITUTE(B2,"元",REPT(" ",5)),ROW($1:$40),7),),2,6)

5、去粗取精，结尾仍是精彩的构成部分

外层的TEXT()，保留数值，把文本以0替换，为SUMPRODUCT()正确求和提供了保证。

相信大家能很好领会这个函数的作用。

如果以金额开头，这个金额将会被忽略，道理你一定明白。如果问题不会有这种情况，可以不考虑它。这是题外话。

wddn

QUOTE:

以下是引用xj4587在2008-9-3 20:07:20的发言：

暂时支持不到小数。金额不可写第一位，相当经典

小数，负数的问题，版主们已经解决。

金额第一位，是这意思：

=SUMPRODUCT(--TEXT(MID(TEXT(MID(SUBSTITUTE("☆"&B2,"元",REPT(" ",15)),ROW($1:$99),15),),2,15),"0;;;!0"))

shaowu459

这个函数真是写得好，方法巧妙，好好学习。

如果不会写公式的时候老板还催的话，我觉得可以这样处理：复制这一列，然后分列，按照分隔符"罚",这样就分成两列了，然后选中这两列，查找替换"元*"为空，这样就可以只剩下数字了。

thurna

8楼公式可以解释下么？

wshcw

支持小数,负数问题公式:

=SUMPRODUCT(--TEXT(MID(TEXT(MID(SUBSTITUTE(B2,"元",REPT(" ",15)),ROW($1:$99),15),),2,15),"[<>]G/通用格式;;;!0"))

[此贴子已经被作者于2008-9-4 18:23:33编辑过]

aichong

支持小数、负数：

=SUMPRODUCT(--TEXT(MID(TEXT(MID(SUBSTITUTE(B2,"元",REPT(" ",15)),ROW($1:$99),15),),2,15),"G/通用格式;-G/通用格式;;!0"))