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

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

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

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

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

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

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

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


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

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


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


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


功能六:智能換行


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


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

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

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


職場(chǎng)必殺技 11招讓自己厲害100倍


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


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