【公式解析系列】数据按分隔符“分列”函数解法解析

gouweicao78

原文：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的【必杀技】公式：

1. =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))
得到结果：

1

2

zhangsa

n

北京

Excelt

明显有问题，不用细分析了。

2、是否空格数只要不少于原数据“全长”就可以？我们用24代替，测试公式：
得到结果：

1

2

zhangsan

北京

Exceltip

正确，但是否保险？查一下最后一“列”看看
即对MID(SUBSTITUTE($A4,",",REPT(" ",24)),24*5-23,24)部分按F9得到{"            Exceltip"}
明显看出，最后一列内容右边已无空格，刚好达到临界状态。

由此，我们得到了通用公式为：

1. =TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",LEN($A2))),LEN($A2)*(COLUMN(A:A)-1)+1,LEN($A2)))

复制代码

[ 本帖最后由 gouweicao78 于 2010-9-26 09:41 编辑 ]

wjzwang

讲得很透彻,学习了

橙子125

非常好，学习了

wshcw

REPT就象一根橡皮筋,可以任意拉长.把需要保护的字段推向后面,让MID提不到的.

lb_bn

版主辛苦，学习了。

水中月2009

请问版主，如何转换成行？

gouweicao78

比如：
=TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",50)),50*COLUMN(A:A)-49,50))
改为：
=TRIM(MID(SUBSTITUTE(A$2,",",REPT(" ",50)),50*ROW(1:1)-49,50))向下复制

水中月2009

学习了，谢谢！

wingpan

版主的解释总能举重若轻.

水中月2009

请教版主，公式中   50*ROW(1:1)-49 什么意思嘛？