什么意思呢,打个响指,我举个例子…… 如下图所示,A列是数据源,每个单元格的数据以分隔符"/"组合在一起,例如:"看见/星光/Excel"。 B2单元格公式如下:
FIND函数寻找"/"在A2中首次出现的位置,并减1扣掉该分隔符,然后使用LEFT函数从左向右提取该长度的字符即为结果。 有朋友可能会好奇被查找字符串为啥是A2&"/",而不是A2,这是为了避免FIND在A2中查不到结果时返回错误值,例如A4单元格。 第2种情况,提取最后一个间隔符"/"后的数据 这个问题和第一个问题刚好相反…… B2单元格公式如下:
SUBSTITUTE(A2,"/",REPT("",100))部分,将A2中的"/"替换为100个空格,然后RIGHT函数从右边提取100个字符,这100个字符必然包括了最后一个"/"后的数据以及大部分空格,因此最后用TRIM函数清除空格即为结果。 3,提取指定位数间隔符之间的数据 例如提取第1个"/"和第2个"/"之间的数据。 B2单元格公式如下:
4,按指定间隔符将数据拆分到多个单元格 B2输入以下公式,向右向下复制填充至B2:E5区域,即可得出结果:
该公式和第3个公式类似,只是使用COLUMN(A1)*100-99取动态区间,随着公式的向右拖动,依次提取第1~101~201个字符起的100个字符结果,最后依然使用TRIM函数清理空格。 这和Excel自带的【分列】功能十分相似,那为什么不直接用分列呢? 函数相比分列等基础操作还是有优势的。分列是手动的,没有建立结果和数据源之间的动态关联。而函数有建立这样的动态关联,它可以实现自动运算,也可以嵌套在它函数内实现模版化应用。 …… TRIM+MID+SUBSTITUTE是一个非常经典的字符串处理函数套路,尤其擅长处理分隔符的问题,如果不能一次性掌握,也建议收藏备用~ 除了该套路外,从Excel 2013版开始,也可以通过FILTERXML函数实现同类功能,例如,获取第1个和第2个分隔符"/"之间的数据,参考公式如下:
将字符串按分隔符"/"拆分为多列,公式如下:
该函数涉及XML语言,以后有机会咱们再开个单章细聊,这里就先做个了解。 |
|
来自: asaser > 《No2:函数公式》