|
今天分享的数据处理技巧是按指定间隔符整理提取数据……
什么意思呢,举个例子……
如下图所示,A列是数据源,每个单元格的数据以符号“/”作为间隔组合在一起,例如:“看见/星光/Excel”。
1
提取第一个间隔符"/"前的数据,如下图所示的B列计算结果。
公式:
=LEFT(A2,FIND("/",A2)-1)
FIND函数发现"/"在A2单元格中首次出现的位置,然后使用LEFT函数从左向右提取该长度的字符,即为结果。
2
提取最后一个间隔符"/"后的数据。如下图所示的B列计算结果。
这个问题和第一个问题刚好相反……
公式:
=TRIM(RIGHT(SUBSTITUTE(A2,"/",REPT(" ",100)),100))
SUBSTITUTE(A2,"/",REPT(" ",100)),这部分公式将A2中的"/"替换为100个空格,然后RIGHT函数从右边提取100个字符,这100个字符必然包括了最后一个"/"后的数据以及大部分的空格,因此最后用TRIM函数清除空格即为结果。
3
提取指定位数间隔符之间的数据,例如提取第2个"/"和第3个"/"之间的数据。
公式:
=TRIM(MID(SUBSTITUTE(A2,"/",REPT(" ",100)),100,100))
和第2个公式有些相似,依然先使用SUBSTITUTE函数,将A2单元格中的"/"替换为100个空格,这样就将不同的值划分到了由空格间隔而成的多个小房子里,再使用MID函数从指定区段取值,最后使用TRIM函数消除空格。
4
按指定间隔符将数据拆分到多个单元格,类似于【分列】功能。
B2输入以下公式,向右向下复制填充至B2:E5区域,即可得出结果:
=TRIM(MID(SUBSTITUTE($A2,"/",REPT(" ",100)),COLUMN(A1)*100-99,100))
该公式和第3个公式类似,只是使用COLUMN(A1)*100-99取动态区间,随着公式的向右拖动,依次提取第1~101~201个字符起的100个字符结果,最后依然使用TRIM函数清理空格。
5
小贴士
TRIM+MID+SUBSTITUTE是一个非常经典的字符串处理函数套路,尤其擅长与处理间隔符的问题,如果不能一次性掌握,也建议收藏备用哦~
6
数据分列的大神Ctrl+E
更有一秒数据分列的大神Ctrl+E
见识过Ctrl+E的威力之后,便深深的迷恋上了它,直呼“太好用了!要逆天了!”于是闭关数日专心修炼,终得神功大成。为了感谢大家对92爱知趣的关注,小艾特委托小编将此神功分享,赠与有缘之士。
Ctrl+E到底有多逆天,咱闲话不多话,看功能。
功能一:合并多列数据
比如我们要把A列的江湖门派,B列的职位,C列的姓名合并在一起,只需在D2输入对应的信息,然后按Ctrl+E,闪电般的填充完了
功能二:拆分数据
如下图,我们需要把A列的长宽高分别拆分到对应的BCD列,同样在BCD列分别来个栗子,然后按Ctrl+E
功能三:银行卡号分段显示
像银行卡,手机号这些较长的字符串,加个空格是不是更清楚了,还是奉上Ctrl+E
功能四:文字顺序的调整
Ctrl+E…..
功能五:批量添加前缀
我们要对A列内容批量添加前缀,同理,举栗,Ctrl+E
功能六:智能换行
功能七:身份证中提取出生年月
没有对比就没有伤害,想想曾经加班埋头于复杂的公式,小编的内心是这样滴
人说情人总是老的好,我说版本还是新的好,Ctrl+E智能填充仅支持2013/16版本,你会为了他抛弃你的老情人吗?反正小编是早已换了新欢了。
财会人必备的一套财务报表(42个)
职场必杀技 11招让自己厉害100倍
Excel录入小技巧,帮你整理好了,分分钟学会!
施工单位日常合同模版300个,限时免费下载
|
|