整数据,一个单元格中全按分号分隔数据,网上有SUBSTITUTE()函数将原字符替换成CHAR(10)换行符,然后粘贴成文本再复制到原表格中。本文档按函数拆分,略过复制粘贴过程,仅能处理较少的数据,以分号为例
1个分号:
=MID(原表!$B$29,1,FIND(";",原表!$B$29))
=MID(原表!$B$29,FIND(";",原表!$B$29)+1,LEN(原表!$B$29)-FIND(";",原表!$B$29))
2个分号:
=MID(原表!$B$29,1,FIND(";",原表!$B$29))
=MID(原表!$B$29,FIND(";",原表!$B$29)+1,FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1)-FIND(";",原表!$B$29))
=MID(原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1)+1,LEN(原表!$B$29)-FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1))
3个分号:
=MID(原表!$B$29,1,FIND(";",原表!$B$29))
=MID(原表!$B$29,FIND(";",原表!$B$29)+1,FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1)-FIND(";",原表!$B$29))
=MID(原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1)+1,FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1)+1)-FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1))
=MID(原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1)+1)+1,LEN(原表!$B$29)-FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1)+1))
4个分号:
=MID(原表!$B$29,1,FIND(";",原表!$B$29))
=MID(原表!$B$29,FIND(";",原表!$B$29)+1,FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1)-FIND(";",原表!$B$29))
=MID(原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1)+1,FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1)+1)-FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1))
=MID(原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1)+1)+1,FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1)+1)+1)-FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1)+1))
=MID(原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1)+1)+1)+1,LEN(原表!$B$29)-FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1)+1)+1))
5个分号:
=MID(原表!$B$29,1,FIND(";",原表!$B$29))
=MID(原表!$B$29,FIND(";",原表!$B$29)+1,FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1)-FIND(";",原表!$B$29))
=MID(原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1)+1,FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1)+1)-FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1))
=MID(原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1)+1)+1,FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1)+1)+1)-FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1)+1))
=MID(原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1)+1)+1)+1,FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1)+1)+1)+1)-FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1)+1)+1))
=MID(原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1)+1)+1)+1)+1,LEN(原表!$B$29)-FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29,FIND(";",原表!$B$29)+1)+1)+1)+1))