一個既能分行又能分列的文本拆分函數(shù)TEXTSPLIT
?
作者:老菜鳥來源:部落窩教育發(fā)布時間:2023-05-26 10:12:32點擊:2710
編按:
今天要給大家分享一個既能分行又能分列的函數(shù):TEXTSPLIT。該函數(shù)當前為office 365 專供,是最強大的文本拆分函數(shù)。
TEXTSPLIT,一個同時支持分行分列功能的文本拆分函數(shù)。它既能分行又能分列;既能按一個分隔符拆分又能按多個分隔符拆分;既能單獨拆分成多行或多列又能同時拆分為多行多列。
此函數(shù)用于拆分一些不規(guī)范數(shù)據(jù)甚是靈活好用。
舉個例子,想把一個單元格里的多項內容分開,通常使用的都是分列功能,但使用=TEXTSPLIT(B2,"、")就可以一步到位。
此公式就是將B2單元格的內容按照頓號分列。
如果你以為TEXTSPLIT僅僅是一個按照指定符號拆分文本的話,那就太小看這個函數(shù)了。
TEXTSPLIT函數(shù)一共有六個參數(shù),分別如下:
? Text:要拆分的文本,必填。
? col_delimiter:列分隔符,可以將數(shù)據(jù)拆分到多列。
? row_delimiter:行分隔符,可以將數(shù)據(jù)拆分到多行。
? ignore_empty:是否忽略連續(xù)分隔符,True表示忽略連續(xù)分隔符;False表示不忽略連續(xù)分隔符,不寫默認為False。
? match_mode:是否區(qū)分大小寫的匹配。1為不區(qū)分大小寫,0為區(qū)分大小寫,默認為0。
? pad_with出錯時填充的值,不指定時錯誤值顯示為#N/A。
下面就通過一些示例帶大家來見識一下這個分行分列都很強的函數(shù)!
說明:以下示例都是在Excel365演示的,配合Excel365的數(shù)組自動擴展功能使用。
文本分行分列1:將數(shù)據(jù)拆分成多行
公式=TEXTSPLIT(INDEX(B:B,MATCH(D2,A:A,0)),,"、")可以將指定姓名的報銷事由拆分到同一列的多個單元格。
公式中的INDEX(B:B,MATCH(D2,A:A,0))是按照姓名匹配對應的報銷事由。
后面連續(xù)兩個逗號表示第二參數(shù)忽略,第三參數(shù)使用頓號將數(shù)據(jù)拆分到多行。
文本分行分列2:按多個分隔符拆分數(shù)據(jù)
公式=TEXTSPLIT(B2,{":",";"})可以將單元格的數(shù)據(jù)按照冒號和分號兩種分隔符進行拆分,效果如圖所示。
注意:第二參數(shù)使用多個分隔符的時候,要將分隔符至于一組大括號內。
文本分行分列3:將單元格的數(shù)據(jù)拆分成多行多列
公式=TEXTSPLIT(VLOOKUP(D2,A:B,2,0),":",";")可以將指定姓名的報銷事由拆分成兩列多行的數(shù)據(jù)。
在這個公式中,第二參數(shù)用的是冒號拆分至多列,第三參數(shù)用的是分號拆分至多行。
文本分行分列4:忽略連續(xù)分隔符拆分成多列
在實際應用中,會存在連續(xù)分隔符的情況,最常見的是用空格做分隔符,例如下面這個示例:
數(shù)據(jù)源中藍色箭頭所指的位置實際是兩個空格,直接使用公式=TEXTSPLIT(B2," ")按照空格分列時就會出現(xiàn)空單元格。
為了避免這種情況,就需要用到TEXTSPLIT的第四參數(shù),公式修改為:=TEXTSPLIT(B2," ",,1)
文本分行分列5:用指定值取代同時分行分列出現(xiàn)的錯誤值
有時候遇到數(shù)據(jù)源不完整有缺失的時候,用公式將數(shù)據(jù)同時分行和分列就會出現(xiàn)錯誤值。
TEXTSPLIT函數(shù)的最后一個參數(shù)就是專門應對這種情況的,公式可以修改為:=TEXTSPLIT(VLOOKUP(D2,A:B,2,),":",";",,,"未填寫")
文本分行分列6:按字母拆分時區(qū)分大小寫
注意數(shù)據(jù)源里的數(shù)字之間有字母a和A,公式=TEXTSPLIT(A2,"A",,,1)可以不區(qū)分大小寫進行拆分,結果如下。
如果將公式里1改成0,表示區(qū)分大小寫,結果就變成這樣了,嚴格按照字母A進行拆分。
通過以上的6個示例,相信大家對于TEXTSPLIT的功能已經(jīng)有所了解,最后再來分享一個示例,將A、B兩列數(shù)據(jù)源拆分成了類似工資條的效果。
公式為:=TEXTSPLIT(TEXTJOIN("、",1,SUBSTITUTE(A2:B12,"、",",")),",","、",,,"")
好的,以上就是今天要分享的可分行分列的文本拆分函數(shù)Textsplit的用法,感謝大家的觀看。
本文配套的練習課件請加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關注公眾號,可隨時隨地學習Excel:
相關推薦:
如何拆分不規(guī)則字符?除了Ctrl+E,還有這兩招!
八大查找函數(shù)公式,輕松搞定數(shù)據(jù)中的多條件查找
版權申明:
本文作者老菜鳥;同時部落窩教育享有專有使用權。若需轉載請聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復,Excel卻判定數(shù)據(jù)重復,這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!