【公式解析系列】之单元格内多字段对应数据分离查询

楼主 gouweicao78	【附件】【公式解析系列】之单元格内多字段对应数据分离查询.rar 如图，工作中经常B2单元格中有这样的数据，貌似很规范（如果是在一个表的2列单元格中，就可以用VLOOKUP简单查询了）。实际上没这么简单。数据为：楦型：D 面料：SAMURAI/FORESTA 内里：VITELLO GRIGIO 大底：GQ07 【数据特点】：1、“字段”与“数据”之间用冒号相隔；2、“字段”与“字段”之间在不同行（强制换行）。【问题】如何根据D1:G1的“字段名”将字符分离到D2:G2。【重要链接】：【公式解析系列】之数据按分隔符“分列”函数解法【解决方案1】 D2单元格，普通公式，向右复制，公式长137字符： =TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B2,$D$1,),$E$1,),$F$1,),$G$1,),"：",REPT(" ",50)),COLUMN(B:B)*50-49,50)) 【思路解析】 1、利用SUBSTITUTE函数多次替换，将B2中的“字段”替换掉 2、再将冒号作为“分隔符”，进行“函数分列”法，分离字符串。【解决方案2】 D2单元格，普通公式，向右复制，公式长67字符（需在H1单元格输入☆作为辅助单元格）： =REPLACE(LEFT($B2,FIND(E$1&"：",$B2&"☆：")-1),1,FIND(D$1&"：",$B2)+2,) 【思路解析】见图解 1、LEFT函数+FIND（下一字段名，截取到当前字段数据为止的字符 2、REPLACE+FIND冒号，去除数据前冒号以及之前的字符【局限】：解法1、2要求B2中的“字段”与D2:G2必须按照顺序排列并一一对应，否则出现错位，也不能有漏项。事实上，数据项目往往不一样。继续看下面解法3。
2楼 gouweicao78	D2单元格普通公式，长度126字符 =SUBSTITUTE(VLOOKUP(D$1&"",TRIM(MID(SUBSTITUTE($B2&CHAR(10)&D$1&"：",CHAR(10),REPT(" ",50)),{0;1;2;3}50+1,50)),1,0),D$1&"：",) 【思路解析】 1、TRIM部分进行“函数分列”法分离字符，以Char(10)即换行符为分隔符。其中： $B2&CHAR(10)&D$1&"："就是在B2的字符后面再加1行、1个字段名（D1，可变的），以防止出现没有该字段的情况 “分列”完的结果（以缺少项目的B3为例）： {"面料：SAMURAI/FORESTA";"内里：VITELLO GRIGIO";"大底：GQ07";"面料："}——D3“分列”后结果 {"面料：SAMURAI/FORESTA";"内里：VITELLO GRIGIO";"大底：GQ07";"楦型："}——E3“分列”后结果红色部分是多余的，却是可以避免出错的，就像E3。 2、VLOOKUP(D$1&"",trim分列结果,1,0) 这是本解法的精妙之处，一直以来，VLOOKUP多用于2列以上查找，一想到单列，很多人想的是LOOKUP，却忽视了VLOOKUP的一个非常有用的特性“支持通配符”与“精确匹配”查找结合——实现“精确的模糊查找”。呵呵，很矛盾的词，不过很有意思的用法。 VLOOKUP第3个参数为1，表示在“本列”查找。因此TRIM得到的必须是“一列”，如果是“一行”，就应该换HLOOKUP了。在D3的公式中，VLOOKUP将找到第一个“面料：”对应的字符"面料：SAMURAI/FORESTA"，而不是第2个；在E3的公式中，VLOOKUP将找到的是"楦型："* 接下来，就是把字段名和冒号去除掉。【特色】不受D1:G1的字段名排序影响、不受B列缺项漏项影响。
3楼 APOLLO	精妙! 测试原数据发现：在所有的CHAR(10)字符之前均有一个CHAR(13)字符，故楼主的公式返回结果包含该多余字符，再应用一层SUBSTITUTE()函数消除
4楼 APOLLO	公式修改一下： =SUBSTITUTE(VLOOKUP(D$1&"",TRIM(MID(SUBSTITUTE($B2&CHAR(10)&D$1&"：",CHAR(13)&CHAR(10),REPT(" ",50)),{0;1;2;3;4;5;6}50+1,50)),1,0),D$1&"：",)
5楼 APOLLO	下测试：下面代码适应所有情况 =IF(ISBLANK(D$1),"",IFERROR(LEFT(SUBSTITUTE($B2,LEFT($B2,FIND(D$1,$B2)+LEN(D$1)),""),IFERROR(FIND(CHAR(13),SUBSTITUTE($B2,LEFT($B2,FIND(D$1,$B2)+LEN(D$1)),""))-1,LEN(SUBSTITUTE($B2,LEFT($B2,FIND(D$1,$B2)+LEN(D$1)),"")))),""))
6楼 yzcyzc1023	公式怎么复杂，怎么学的会。
7楼 gouweicao78	因为较为复杂，所以我特此进行【解析】，呵呵。
8楼 wangqilong1980	收藏,慢慢学习
9楼 LOGO	学习了..
10楼健康快乐123	确实很复杂，等到用时在研究
11楼冯晶晶2011	实在太复杂了，