ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
楼主: 天天都爱

[已解决] [求助]文本中提金额

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2014-6-9 23:01 | 显示全部楼层
本帖已被收录到知识树中,索引项:拆分和提取
36楼x.f.zhao的公式才是完美解法,万能解法:=SUMPRODUCT(-TEXT(MID(TEXT(MID(SUBSTITUTE("A"&B2,"元",REPT(" ",16)),ROW($1:$99),17),),2,16),"-0%;0%;;!0"))

TA的精华主题

TA的得分主题

发表于 2014-7-23 09:46 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2014-7-23 10:07 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
第三页山菊花版主的解释收藏

TA的精华主题

TA的得分主题

发表于 2014-7-23 10:34 | 显示全部楼层
x.f.zhao 发表于 2008-9-3 10:46
=SUMPRODUCT(--TEXT(MID(TEXT(MID(SUBSTITUTE(B2,"元",REPT(" ",15)),ROW($1:$99),15),),2,15),"0;;;!0"))

八楼的兄弟太牛逼!!!

TA的精华主题

TA的得分主题

发表于 2014-7-23 11:01 | 显示全部楼层
留个记号 方便学习
8楼x.f.zhao的公式
=SUMPRODUCT(--TEXT(MID(TEXT(MID(SUBSTITUTE(B2,"元",REPT(" ",15)),ROW($1:$99),15),),2,15),"0;;;!0"))
25楼山版的解释

向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()正确求和提供了保证。
相信大家能很好领会这个函数的作用。
如果以金额开头,这个金额将会被忽略,道理你一定明白。如果问题不会有这种情况,可以不考虑它。这是题外话。

TA的精华主题

TA的得分主题

发表于 2014-7-23 16:05 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
  1. =SUM(--(0&LEFT(MID(SUBSTITUTE(B2,"罚","罚"&REPT(" ",100)),SMALL(IF(MID(B2,ROW($1:$100),1)="罚",ROW($1:$100),4^8),ROW($1:$100))+1+100*ROW($1:$100),99),FIND("元",MID(SUBSTITUTE(B2,"罚","罚"&REPT(" ",100)),SMALL(IF(MID(B2,ROW($1:$100),1)="罚",ROW($1:$100),4^8),ROW($1:$100))+1+100*ROW($1:$100),99)&"元",1)-1)))
复制代码

JCYp10vQ (1).rar

3.49 KB, 下载次数: 10

TA的精华主题

TA的得分主题

发表于 2014-11-18 12:59 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2014-11-28 14:22 | 显示全部楼层
一直没用过SUBSTITUTE函数,看帖子学习了

TA的精华主题

TA的得分主题

发表于 2014-11-29 17:17 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2014-12-4 11:25 | 显示全部楼层
这个函数看了我好多天了,一点点琢磨啊
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-6-16 00:00 , Processed in 0.039325 second(s), 8 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表