一 问题
如果你要处理这样的数据,比如把这样的一个单元格里的字符串带为多列(或多行),甚至你本身表里就有多列这样的数据
4;5;7 | 100;2;999 |
由于不能贴完整表,先做情况假设:你的这个单元格是A42,后面是B42。咱们怎么处理A42,B42看的同学如果有兴趣,可以再练习下
二公式
公式本体:TRIM(MID(SUBSTITUTE($A42,";",REPT(" ",100)),(COLUMN(A42)-1)*100+1,100))
然后往右拖为多列
详细拆解每个公式说明
trim() | 去掉空格,英文字符串有特别 |
mid() | 文本,开始数,总长度 |
rept() | 在一个单元格中,指定次数复制文本 |
substitute() | 替换文本:文本,旧内容,替换旧内容的新内容,(默认全替换)第几个要替换 |
三 遇到的问题和处理方法&总结
问题1 | 为什么要用这种$A42 $A$42,不用A42 |
因为你的原始数据放在一个单元格,但却要拆为多列,至少也得把 列号锁住把, | |
如果是拆为多行,则需要锁行数就得是A$42 | |
至于$A$42,可能是1个数据不需要拖表把 | |
问题2 | 为什么要填rept()的100,而不是10,或1 |
懂的人估计会填1001,101,11,2,而不是1000,100,10,1 | |
因为至少也得是2,才能处理一个分隔符的 | |
你自己看下原数数据具体决定 | |
懒得看保险起见就填 101 这种,100,,99也行,但也别1001,10001等,算的太慢了,效率问题 | |
问题3 | 原数数据并不放在第一列的问题 |
比如原数数据在C1了,但里面必须还要写COLUMN(A1) | |
因为COLUMN(A1)和原数数据无关,是为了取数组1,2,3,4.。。。 | |
如果是拆位多行,就得仍然取row(A1) | |
问题4 | 之前都是分多行 |
分多行问题:如果想把一列的数分成,多个行 TRIM(MID(SUBSTITUTE(A$42,";",REPT(" ",100)),(ROW(A1)-1)*100+1,100)) 然后往下拖动 |
五,未完成的内容---以后我会来继续补充,加强这个点
未完成内容(备忘) |
或者分列的方法,写成VBA,VBA的还要分别研究 |
其他的EXCEL方法,还有几种不太好的 |
还有其他的好的,都写上,还可以以后补全 |
PHONETIC()函数 |
六 备忘
(1) 我决定把本地的学习经验搬到网上了,因为本身学和用EXCEL时经常会查网上帮助,希望自己的经验也能帮助别人和自己
(2)发现CSDN真好用,表格能贴上去,显示格式真舒服
尴尬,第一次提交后,发现表格默认很窄,需要自己手动拉大。。。
(3)编程真好,我一定要学会
(4)这是我EXCEL经验的第一个,我会努力整理和写上来,另外考虑要不要上B站搞个视频,毕竟这里主要是文章
更新,发现的问题
有些较长的字符串,拆分公式返回报错
- 有些较长的字符串,拆分公式返回报错
- 尤其是公式里用 1001等,更容易,因为,这相当于把字符串 加长了 特殊符号个数N*1001 这么长,可能超了
- 但是如果改成99等,又会因为,可能99 不能远超过原字符串长度,导致中间拆分的部分数据错误,很明显的标志就是,你会发现拆分出来的个数,多于 分隔符的个数了!!
- 据说最初的字符串长度是32767,也就是大概32000左右,用这个公式要注意,替换字符串后步能超过字符串的最长长度了!!!!
- 要拆分的字符串如果很长,分割符很多,就要小心!!!
暂时的解决办法(另外,已经考虑用VBA拆分了)
- 1分段,保证每段都小于300个字符
- 2用1001这种,远大于本身字符串长度,安全,别用101这种太小了
- 3最后再合并
- 4直接用1001这种较大的空格个数,可能导致溢出长度报错查不出来
- 5不行用VBA来分段,因为公式分段,有个符号的话很麻烦,还要多个步骤