|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
原文:http://www.exceltip.cn/thread-2188-1-4-2.html
数据按固定分割符号“分列”是一项常用的基础应用功能之一,通过函数公式的也能解决,一般看数据情况而定,比如简单的“2列”数据像"数据1,姓名2",一般的LEFT或MID、REPLACE等与FIND配合即可解决,但是有的数据比较多“列”的比如"1,2,zhangsan,北京,Exceltip"等,如图:
一次次FIND恐怕就很累人了,于是就有了一个TRIM+MID+SUBSTITUTE+REPT的【必杀技】公式:- =TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",50)),50*COLUMN(A:A)-49,50))
复制代码 【解析】
1、挑拨离间:利用SUBSTITUTE+REPT组合,将分隔符——逗号替换成固定长度的空格,将各“列”数据距离拉大,结果如下:
"1+50个空格2+50个空格zhangsan+50个空格北京+50个空格Exceltip"
2、各个击破:MID函数取出含有空格的各“列”的值
第1段从第50*1-49即第1个字符起,取50个字:{"1 "}——"1+49个空格"
第2段从第50*2-49即第51个字符起,取50个字:{" 2 "}——"+1个空格2+48个空格"
第3段从……即第101个字符起,取50个字:{" zhangsan "}——"+2个空格zhangsan+40个空格"
以此类推,得到每一列数据和“空格”的组合文本。
3、去伪存真:这些空格都不是我们要的,利用TRIM函数特性,即可将它们去除。
【疑问】这REPT的50个空格是怎么定的?换做其它数行吗?
还是这个例子"1,2,zhangsan,北京,Exceltip"——共有24个字符,最长的一“列”是zhangsan和Exceltip,都8个字的。
我们来测试一下:
1、是否空格数只要比最长“列”多就可以?我们用9来代替,测试公式:
=TRIM(MID(SUBSTITUTE($A3,",",REPT(" ",9)),9*COLUMN(A:A)-8,9))
得到结果:
明显有问题,不用细分析了。
2、是否空格数只要不少于原数据“全长”就可以?我们用24代替,测试公式:
得到结果:
正确,但是否保险?查一下最后一“列”看看
即对MID(SUBSTITUTE($A4,",",REPT(" ",24)),24*5-23,24)部分按F9得到{" Exceltip"}
明显看出,最后一列内容右边已无空格,刚好达到临界状态。
由此,我们得到了通用公式为:- =TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",LEN($A2))),LEN($A2)*(COLUMN(A:A)-1)+1,LEN($A2)))
复制代码
[ 本帖最后由 gouweicao78 于 2010-9-26 09:41 编辑 ] |
评分
-
2
查看全部评分
-
|