excel 拆分单元格,单元格内容怎么拆分出来( 二 ) _知识分享

图8
（4）公式说明：
A、拆分左边“字母数字”的公式：
=LEFT(A1,LOOKUP(1,0/((LENB(MID(A1,ROW($1:$15),1))=1)*(LENB(MID(A1,ROW($2:$16),1))=2)),ROW($1:$15)))
① 先用 MID(A1,ROW($1:$15),1) 把 A1 中的每个文字拆分开，结果为：
{"W";"S";"-";"560";"长";"袖";"白";"衬";"衫";"8";"6";".";"9"}，是怎么返回这个数组的？ROW($1:$15) 返回一个 1 到 15 的数组（15 表示 A1 中的字符总数），即 {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}，Mid 每次从数组中取出一个元素作为开始截取参数，并每次截取一个字符；例如：第一次从数组中取出 1，即从第一位开始截取，截取一个字符，返回 "W"；第二次从数组中取出 2，即从第二位开始截取，截取一个字符，返回 "S"；以此类推直到取完数组中的所有元素。
② 则 LENB(MID(A1,ROW($1:$15),1)=1 变为 LENB({"W";"S";"-";"560";"长";"袖";"白";"衬";"衫";"8";"6";".";"9"})=1，LenB 依次从数组中取出每一个元素，并返回它们的字节数，结果变为 {1,1,1,1,1,1,2,2,2,2,2,1,1,1,1}=1，因为每个字母数字的字节数为 1，每个汉字的字节数为 2 。然后再取数组中的每一个元素与 1 比较，如果等于 1，则返回 True，否则返回 False，最后返回{True,True,True,True,True,True,False,False,False,False,False,True,True,True,True} 。
③ LENB(MID(A1,ROW($2:$16),1))=2 与 LENB(MID(A1,ROW($1:$15),1))=1 是一样的道理，所不同的是它从第二位开始把 A1 中的每个文字拆分开，也就是舍弃第一个字，为什么要这样拆分？LENB(MID(A1,ROW($2:$16),1))=2 返回的结果为 {1,1,1,1,1,2,2,2,2,2,1,1,1,1,0}=2，然后取出数组中的每个元素与 2 比较，如果相等，则返回 True，否则返回 False，最后返回结果{False,False,False,False,False,True,True,True,True,True,False,False,False,False,False} 。
④ 至此，(LENB(MID(A1,ROW($1:$15),1))=1)*(LENB(MID(A1,ROW($2:$16),1))=2) 变为：
{True,True,True,True,True,True,False,False,False,False,False,True,True,True,True}*
{False,False,False,False,False,True,True,True,True,True,False,False,False,False,False}
接着把两个数组对应的元素相乘，True 被转为 1，False 被转为 0，返回结果为：{0,0,0,0,0,1,0,0,0,0,0,0,0,0,0}，即第 6 个为 1，恰好是 A1 中数字 0 与汉字“长”相交的结果。
⑤ 公式变为 =LEFT(A1,LOOKUP(1,0/({0,0,0,0,0,1,0,0,0,0,0,0,0,0,0}),ROW($1:$15)))，接着用 0 除以数组中的每一个元素，公式变为：
=LEFT(A1,LOOKUP(1,{#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,0,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!},ROW($1:$15)))
只有 0 / 1 = 0，其它都返回分母为 0 错误（即 #DIV/0!）；进一步计算 ROW($1:$15)，它返回{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15} 。
⑥公式变为：
=LEFT(A1,LOOKUP(1,{#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,0,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!},{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}))
接着用 LookUp 在含有 #DIV/0! 的数组中查找 1，由于找不到 1，LookUp 返回小于等于 1 的最大值，所以返回第 6 个 0，然后再根据 0 在数组中的位置在 1 到 15 的数组中查找与 0 的位置（即第 6 位）对应的元素，因此返回 6 。
⑦ 公式变为：=LEFT(A1,6)，最后用 Left 从左边截取 A1 中的文字左边 6 个字符，即 WS-560 。
B、拆分右边部分的公式：=MID(A1,LOOKUP(1,0/(LENB(MID(A1,ROW($1:$15),1))=2),ROW($2:$16)),15)
① LENB(MID(A1,ROW($1:$15),1))=2 返回的值上面已经分析过，结果为{False,False,False,False,False,False,True,True,True,True,True,False,False,False,False}，接着用 0 除以数组中的每个元素，返回结果为 {#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,0,0,0,0,0,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!} 。
② ROW($2:$16) 返回 2 到 16 的数组，即 {2,3,4,5,6,7,8,9,10,11,12,13,14,15,16}，则公式变为：
=MID(A1,LOOKUP(1,{#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,0,0,0,0,0,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!},{2,3,4,5,6,7,8,9,10,11,12,13,14,15,16}),15)
接着用 LookUp 在含有 #DIV/0! 的数组中查找 1，由于找不到 1，所以返回小于等于 1 的最大值，又由于有 5 个 0，所以返回最后一个 0，然后根据返回 0 在数组中的位置在 2 到 16 的数组中找与 0 对应位置的值，即第 11 位的值，该值为 12 。
③ 公式变为：=MID(A1,12,15)，最后用 Mid 把 A1 中的文字从第 12 开始截取，共截取 15 字符，返回 A1 中的文字后面的数字 86.9 。提示：Mid函数如果指定的截取字符数大于文本长度，只截取到末尾。
C、拆分中间的部分公式：=SUBSTITUTE(SUBSTITUTE(A1,B1,""),D1,"")