間隔符提取數(shù)據(jù)的超級公式和數(shù)據(jù)分列大神Ctrl+E

今天分享的數(shù)據(jù)處理技巧是按指定間隔符整理提取數(shù)據(jù)……
什么意思呢，舉個例子……
如下圖所示，A列是數(shù)據(jù)源，每個單元格的數(shù)據(jù)以符號“/”作為間隔組合在一起，例如：“看見/星光/Excel”。

1
提取第一個間隔符"/"前的數(shù)據(jù)，如下圖所示的B列計(jì)算結(jié)果。

公式：
=LEFT(A2,FIND("/",A2)-1)
FIND函數(shù)發(fā)現(xiàn)"/"在A2單元格中首次出現(xiàn)的位置，然后使用LEFT函數(shù)從左向右提取該長度的字符，即為結(jié)果。
2
提取最后一個間隔符"/"后的數(shù)據(jù)。如下圖所示的B列計(jì)算結(jié)果。

這個問題和第一個問題剛好相反……
公式：
=TRIM(RIGHT(SUBSTITUTE(A2,"/",REPT(" ",100)),100))
SUBSTITUTE(A2,"/",REPT(" ",100))，這部分公式將A2中的"/"替換為100個空格，然后RIGHT函數(shù)從右邊提取100個字符，這100個字符必然包括了最后一個"/"后的數(shù)據(jù)以及大部分的空格，因此最后用TRIM函數(shù)清除空格即為結(jié)果。
3
提取指定位數(shù)間隔符之間的數(shù)據(jù)，例如提取第2個"/"和第3個"/"之間的數(shù)據(jù)。

公式：
=TRIM(MID(SUBSTITUTE(A2,"/",REPT(" ",100)),100,100))
和第2個公式有些相似，依然先使用SUBSTITUTE函數(shù)，將A2單元格中的"/"替換為100個空格，這樣就將不同的值劃分到了由空格間隔而成的多個小房子里，再使用MID函數(shù)從指定區(qū)段取值，最后使用TRIM函數(shù)消除空格。
4
按指定間隔符將數(shù)據(jù)拆分到多個單元格，類似于【分列】功能。

B2輸入以下公式，向右向下復(fù)制填充至B2:E5區(qū)域，即可得出結(jié)果：
=TRIM(MID(SUBSTITUTE($A2,"/",REPT(" ",100)),COLUMN(A1)*100-99,100))
該公式和第3個公式類似，只是使用COLUMN(A1)*100-99取動態(tài)區(qū)間，隨著公式的向右拖動，依次提取第1~101~201個字符起的100個字符結(jié)果，最后依然使用TRIM函數(shù)清理空格。
5
小貼士
TRIM+MID+SUBSTITUTE是一個非常經(jīng)典的字符串處理函數(shù)套路，尤其擅長與處理間隔符的問題，如果不能一次性掌握，也建議收藏備用哦~
6
數(shù)據(jù)分列的大神Ctrl+E
更有一秒數(shù)據(jù)分列的大神Ctrl+E
見識過Ctrl+E的威力之后，便深深的迷戀上了它，直呼“太好用了！要逆天了！”于是閉關(guān)數(shù)日專心修煉，終得神功大成。為了感謝大家對92愛知趣的關(guān)注，小艾特委托小編將此神功分享，贈與有緣之士。
Ctrl+E到底有多逆天，咱閑話不多話，看功能。

功能一:合并多列數(shù)據(jù)
比如我們要把A列的江湖門派，B列的職位，C列的姓名合并在一起，只需在D2輸入對應(yīng)的信息，然后按Ctrl+E,閃電般的填充完了


功能二:拆分?jǐn)?shù)據(jù)
如下圖，我們需要把A列的長寬高分別拆分到對應(yīng)的BCD列，同樣在BCD列分別來個栗子，然后按Ctrl+E

功能三:銀行卡號分段顯示
像銀行卡，手機(jī)號這些較長的字符串，加個空格是不是更清楚了，還是奉上Ctrl+E


功能四:文字順序的調(diào)整
Ctrl+E…..


功能五:批量添加前綴
我們要對A列內(nèi)容批量添加前綴，同理，舉栗，Ctrl+E


功能六:智能換行


功能七:身份證中提取出生年月


沒有對比就沒有傷害，想想曾經(jīng)加班埋頭于復(fù)雜的公式，小編的內(nèi)心是這樣滴

人說情人總是老的好，我說版本還是新的好，Ctrl+E智能填充僅支持2013/16版本，你會為了他拋棄你的老情人嗎？反正小編是早已換了新歡了。

財(cái)會人必備的一套財(cái)務(wù)報(bào)表（42個）


職場必殺技 11招讓自己厲害100倍


Excel錄入小技巧，幫你整理好了，分分鐘學(xué)會！


施工單位日常合同模版300個，限時免費(fèi)下載