数字提取组合，用哪个公式简便

悟空师弟 · 发表于 2011-5-4 21:51

原帖由 Christ2004 于 2011-5-3 18:19 发表
。。。。论坛不会这么歧视新人吧，到现在连个愿意好好解释的都没~~~我虽然菜，但是我一直在学习啊，而且百度谷歌都没找到，才这里问的，我觉得不能理解的新人应该不是我一个吧？麻烦高手们出来解释下吧

=TEXT(A1,TEXT(A2,"\0\\0 \0\\0 \0\\0 \0\\0 \0\\0"))
用\这个符号可能一般见得少难理解,改成感叹号!也一样,如下:
=TEXT(A1,TEXT(A2,"!0!!0 !0!!0 !0!!0 !0!!0 !0!!0"))

理解强制符就容易理解这个公式了,而且,把这个公式中出现的5个!0!!0拆开来理解更容易理解:
比如A1内输入1,A2内输入2,公式写成=TEXT(A1,TEXT(A2,"!0!!0"))再来理解:

其中: TEXT(A2,"!0!!0")这部分,是将A2中的2转换成 0!2 ,转换原则是先强制在A2单元格的数值前加个0,再在这个0的后面强制加个!号,即,第1个和第2个感叹号为强制符,第1个0和第3个感叹号是被强制添加的符号,最后一个0是即是A2单元格内的文本型数值.
再将转换后的结果 0!2 作为第一个TEXT的第二个参数,即相当于直接用 TEXT(A1,"0!2"),也就是在A1单元格的数值后面强制加上一个2.变成12.

而楼主问的公式是因为A1和A2单元格内都是5位数,需要重复5次,且中间用空格隔开..

以本帖楼主附件数据
A1:12345
A2:67890
这两个5位数为例:公式=TEXT(A1,TEXT(A2,"\0\\0 \0\\0 \0\\0 \0\\0 \0\\0"))(注意,\号同等于上面所说的感叹号!)
这个公式中
TEXT(A2,"\0\\0 \0\\0 \0\\0 \0\\0 \0\\0") 这部分得到的结果是"0\6 0\7 0\8 0\9 0\0"
此时可得知,如果A2内的数值不变,则公式=TEXT(A1,TEXT(A2,"\0\\0 \0\\0 \0\\0 \0\\0 \0\\0"))同等于=TEXT(A1,"0\6 0\7 0\8 0\9 0\0"),其中\后面的5个数：6、7、8、9、0是要强制添加的字符，\前的5个0代表A1单元格内的各位上的数1、2、3、4、5，结合起来则是：1627384950，中间用空格隔开得到最后结果为 16 27 38 49 50。

不知楼主是否明白？

PS：论坛不会歧视新人，只是不了解楼主对TEXT第二参数的熟悉程度，如果完全不了解，估计我这帖也是白回，如果懂得TEXT中的强制符的用法，这帖又显得多余而且罗嗦，呵呵！

建议：这个公式将TEXT更具体一点是将TEXT中的强制符用到顶级水平，所以，建议楼主先了解单个强制符的用法再一步一步了解这个函数。

悟空师弟 · 发表于 2011-5-4 22:04

原帖由 Christ2004 于 2011-5-4 21:15 发表
...
补充一下：
=TEXT(1845552324,"0!.00,,") 显示为18.46
=TEXT(1845552324,"0.00,,")显示为1845.55
=TEXT(1845552324,"0!.0,,")显示为184.6
=TEXT(1845552324,"!0.0,,")显示为01845.
为什么？？

=TEXT(1845552324,"0!.00,,") 显示为18.46
这与千分位符“,”号与强制符“!”号的用法有关，
取"0!.00,,"这一段来讲：
小数点“.”后面的00,,，一个千分位符间隔3位数，两个间隔6位数，而这6位没有数值占位符0，所以，不显示，结果中的18.46中的点“.”并非真正的小数点，而是通过强制符！即强制在“亿”位后面添加的一个小点，可以在视觉上达到“小数点”的作用，即以亿为单位的数。
提示：两个0加两上千分位符省略的6位数刚好是8位，即亿后面的数。
其它不用多说，楼主自己根据强制添加的小点所在的位置和千分位符的数量再算算就明白了。
另外，麻烦楼主根据这个规则，将123456个用TEXT转换成以“万”为单位的数，并且后面加个“万”字，结果是“12.3万”，如楼主还能将公式帖出来分享分享是最好不过的了。

Christ2004 · 发表于 2011-5-5 10:44

非常感谢悟空老师的解答，我对Text函数只是略知一二（以前自认懂了，只知道用来代替单元格格式设置，原来还差得远，呵呵），对强制符的用法完全不懂，现在总算明白了。
同时也非常感谢Piny老师不厌其烦的解答，通过这个帖子，让我对TEXT函数的理解提升了一大步！
运用之妙，存乎一心。虽然理解了，但真用起来，估计还有很大差距，还得努力学习啊！
第一次来论坛是为了求助，曾经以为自己的Excel水平算高手了（在本公司也的确如此），来之后才发现只是小学生，如果早来个三五年，我之前可以少加很多班啊。。。。

wangyuhu · 发表于 2011-5-5 10:51

原帖由 Christ2004 于 2011-5-5 10:44 发表
非常感谢悟空老师的解答，我对Text函数只是略知一二（以前自认懂了，只知道用来代替单元格格式设置，原来还差得远，呵呵），对强制符的用法完全不懂，现在总算明白了。
同时也非常感谢Piny老师不厌其烦的解答，通过 ...

“如果早来个三五年，我之前可以少加很多班啊。。。。”
有人想加班那就没法了

，我给同事一个公式，十几分钟搞定的数据，他非要不用公式加班搞个一天。领导还以为工作认真呢，（不是针对楼上的哦）

Christ2004 · 发表于 2011-5-5 11:01

另外，麻烦楼主根据这个规则，将123456个用TEXT转换成以“万”为单位的数，并且后面加个“万”字，结果是“12.3万”，如楼主还能将公式帖出来分享分享是最好不过的了。
=TEXT(123456,"0\.0,万") 学会了，真是长见识了，昨天还现学现卖，给别人当了会老师，哈哈

向老师们汇报学习成绩，见帖子：
http://club.excelhome.net/viewth ... p;page=1#pid4854969

另外还得麻烦老师个新问题：
=TEXT(123456,"0\.0,万") 显示为12.3万，=TEXT(123456,"0\.0,W") 显示为12.3W，都是正常的
但=TEXT(123456,"0\.0,wan") 显示值错误，这又是为什么呢，汉字和英文字母可以加里面，单词就不行吗？

wangyuhu · 发表于 2011-5-5 11:10

=TEXT(123456,"0\.0,wa!n")
有几个特殊字符要强制性的，B,D,E,G,H,M,N,S,Y

rhero · 发表于 2011-5-5 11:50

大理版主最强

Christ2004 · 发表于 2011-5-5 12:57

谢谢，又学了一招，受教了！

lt0314 · 发表于 2011-5-6 13:05

膜拜text

zxx212 · 发表于 2011-5-6 13:19

原帖由 wangyuhu 于 2011-5-5 10:51 发表

“如果早来个三五年，我之前可以少加很多班啊。。。。”
有人想加班那就没法了，我给同事一个公式，十几分钟搞定的数据，他非要不用公式加班搞个一天。领导还以为工作认真呢，（不是针对楼上的哦）

的确，有这样的人，工作效率低下，领导还以为他工作认真。。。。。。。

		自动登录	找回密码
密码			免费注册

[求助] 数字提取组合，用哪个公式简便

评分

回复 9楼 wshcw 的帖子