提取、查找、替換數(shù)據(jù)的王者——REGEXP正則函數(shù)
?
作者:ITFANS來(lái)源:部落窩教育發(fā)布時(shí)間:2024-08-06 15:17:29點(diǎn)擊:775
編按:
詳細(xì)介紹WPS新函數(shù)REGEXP正則函數(shù)的用法。
在《兩個(gè)不用嵌套的萬(wàn)能提取公式》文章中我們使用了WPS的正則函數(shù)REGEXP。今天為大家詳細(xì)介紹這個(gè)函數(shù)的強(qiáng)大功能。
1.作用與語(yǔ)法
REGEXP函數(shù)根據(jù)正則表達(dá)式對(duì)字符串進(jìn)行提取、判斷或替換。
=REGEXP(字符串,正則表達(dá)式,[匹配模式],[替換內(nèi)容])
前兩個(gè)參數(shù)必選,后兩個(gè)參數(shù)則可以根據(jù)實(shí)際需要選用。
正則表達(dá)式:就是用一些特殊符號(hào)表達(dá)的字符串規(guī)律。譬如"[0-9]"表示任意單個(gè)數(shù)字,"[A-Z]"表示任意大寫(xiě)單字母。
匹配模式:有三種,0、1、2。0(默認(rèn)值)表示提取,1表示判斷,2表示替換。
替換內(nèi)容:匹配模式為2時(shí)才需要,用于指定替換后的內(nèi)容。若不指定替換內(nèi)容則表示刪除滿足正則表達(dá)式中的字符。
2.最常用正則表達(dá)式字符集
下表簡(jiǎn)要介紹幾種正則表達(dá)式中最常用的字符集及含義。
最常見(jiàn)的字符集 |
含義 |
[0-9] |
0到9的數(shù)字字符集 |
d |
等效于[0-9] |
d{3} |
任意3位數(shù)的字符集。3可以是需要的任何正整數(shù) |
[A-Z] |
A到Z的大寫(xiě)字母字符集 |
[a-z] |
a到z的小寫(xiě)字母字符集 |
[A-z] |
所有大小寫(xiě)字母字符集 |
w |
0到9和A到z的字符集 |
[一-龜] |
常用的漢字字符集 |
[一-龥] |
所有漢字字符集 |
[一-龜]+ |
任意漢字組成的一段連續(xù)字符集 |
[A-z]+ |
任意大小寫(xiě)字母組成的一段連續(xù)字符集 |
[0-9.-]+ |
任意整數(shù)與小數(shù)字符集 |
[A-z].+ |
以字母開(kāi)頭的所有字符集 |
[一-龜].+ |
以漢字開(kāi)頭的所有字符集 |
[^0-9] |
非數(shù)字字符集 |
D |
等效于[^0-9] |
[^A-z] |
非字母字符集 |
[^一-龜] |
非中文字符集 |
W |
非數(shù)字和字母的字符集 |
2. REGEXP基本用法
1)提取模式
提取模式只需使用前兩個(gè)參數(shù)。
譬如提取第一個(gè)漢字前的所有內(nèi)容:
=REGEXP(A2,"^[^一-龜]+")
說(shuō)明:
第一個(gè)^,表示從字符串的開(kāi)頭進(jìn)行匹配;方括號(hào)中的^表示排除,[^一-龜]代表非中文字符,[^一-龜]+表示至少1個(gè)字符長(zhǎng)的非中文字符。
再如提取第一個(gè)漢字起的所有內(nèi)容:
=REGEXP(A2,"[一-龜].+")
2)判斷模式
第三參數(shù)為1就是判斷模式。
譬如判斷下方型號(hào)是否以AB字母開(kāi)頭的:
=REGEXP(A7,"^AB.",1)
說(shuō)明:
.(小圓點(diǎn)),表示任意字符(換行符除外);AB.,表示以AB開(kāi)頭的任意字符。
3)替換模式
第三參數(shù)是2,則進(jìn)行替換操作。
譬如將數(shù)據(jù)中所有A替換為H。
公式:=REGEXP(A14,"A",2,"H")
替換模式下,若第四參數(shù)為空,則表示刪除相應(yīng)字符。
3.典型運(yùn)用
1)按號(hào)段提取手機(jī)號(hào)
譬如提取屬于移動(dòng)的手機(jī)號(hào)。
=IFERROR(REGEXP(A2,"(?<=^|D)(134|135|136|137|138|139|147|150|151|152|157|158|159|187|188|195|197|198)d{8}(?=D|$)"),"非移動(dòng)或號(hào)碼錯(cuò)誤")
說(shuō)明:
(134|135|136|137|138|139|147|150|151|152|157|158|159|187|188|195|197|198) ,是一個(gè)分組, “|”是或邏輯符,用于匹配多個(gè)可能的號(hào)段。
d{8},d是數(shù)字字符集,{8}表示前面的字符出現(xiàn)8次,也就是號(hào)段后出現(xiàn)8位數(shù)字。
以上兩段用于提取符合號(hào)段的11位數(shù)字。
(?<=^|D)表示從字符串開(kāi)頭或者第一個(gè)非數(shù)字字符后開(kāi)始提取。(?<=…),表示某某后的一個(gè)位置;D等效于[^0-9]。
(?=D|$),表示提取的手機(jī)號(hào)位于一個(gè)非數(shù)字或字符串末尾之前。(?=…),表示某某前的一個(gè)位置;$,表示字符串結(jié)尾。
添加(?<=^|D)和(?=D|$)定位提取位置,確保只能從11位數(shù)字中提取手機(jī)號(hào)碼。因此表中兩串超過(guò)11位的紅色號(hào)碼都判定為錯(cuò)誤。
另外,上面的號(hào)碼段分組可以用字符集來(lái)簡(jiǎn)化。
=IFERROR(REGEXP(A2,"(?<=^|D)(13[4-9]|147|15[0-2789]|18[2-478]|19[578])d{8}(?=D|$)"),"非移動(dòng)或號(hào)碼錯(cuò)誤")
2)交換數(shù)據(jù)位置
用括號(hào)將數(shù)據(jù)分組,然后在替換模式下交換數(shù)據(jù)位置。交換中可增加或刪除內(nèi)容。
譬如將“001-原封機(jī)10G”改成“原封機(jī)(舊款)001-10G”。
=REGEXP(A2,"(d{3,4}-)([一-龜]+)",2,"2(舊款)1")
說(shuō)明:
(d{3,4}-),表示將3位或4位數(shù)字加上“-”符號(hào)作為分組1。
([一-龜]+),表示將任意字?jǐn)?shù)的漢字作為分組2。
2(舊款)1,表示將分組2加上字符“(舊款)”并放在分組1之前,從而實(shí)現(xiàn)了數(shù)據(jù)位置的交換。
3)文本中的數(shù)字運(yùn)算
可以用REGEXP提取出所有數(shù)字,然后進(jìn)行運(yùn)算。
比如下表是產(chǎn)品長(zhǎng)、寬、高數(shù)值,現(xiàn)在需要計(jì)算體積。
=PRODUCT(--REGEXP(A18,"[d.]+"))
說(shuō)明:
[d.]+,表示所有正整數(shù)與小數(shù)。
4)復(fù)雜數(shù)據(jù)分列
下表需要將系統(tǒng)導(dǎo)出數(shù)據(jù)分成4列,但可用的分隔符只有一個(gè)逗號(hào)。
=REGEXP(A2,{"d{3}","[一-龜]+","d{11}","(?<=,)d{4}"})
說(shuō)明:
REGEXP函數(shù)的第二參數(shù)支持?jǐn)?shù)組,此處用數(shù)組分別提取實(shí)現(xiàn)了分列。
d{3},表示提取3位數(shù)字。此處滿足3位數(shù)字提取的有多個(gè),但在數(shù)組下,只會(huì)返回第一個(gè)結(jié)果,就是最前面的編號(hào)。
(?<=,)d{4},表示從逗號(hào)后提取4位數(shù)字。
5)多分隔符提取數(shù)據(jù)
直接用非分隔符字符集進(jìn)行提取。
=REGEXP(A12,"[^, /、]+")
說(shuō)明:
數(shù)據(jù)中的分隔符包括英文逗號(hào)、空格、斜杠、頓號(hào)。[^, /、]+,可以得到除分隔符外的任意字符組合。
如果大家對(duì)正則表達(dá)式的學(xué)習(xí)有興趣,請(qǐng)留言,若需要的同學(xué)較多,我們將安排出教程。
本文配套的練習(xí)課件請(qǐng)?zhí)砑涌头⑿?/font>buluowojiaoyu索取。
做Excel高手,快速提升工作效率,部落窩教育Excel精品好課任你選擇!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
版權(quán)申明:
本文作者ITFANS;部落窩教育享有稿件專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集!(建議收藏)-下篇
- 明明沒(méi)有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問(wèn)題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說(shuō)是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!
最新教程
- SHEETSNAME,一鍵獲得所有工作表名稱完成目錄制作
- 延遲退休落地,快速查詢你上幾年才退休!
- 快速將PDF電子發(fā)票數(shù)據(jù)提取到發(fā)票明細(xì)登記表的方法
- 提取、查找、替換數(shù)據(jù)的王者——REGEXP正則函數(shù)
- Excel表10萬(wàn)行數(shù)據(jù),篩選卡頓怎么辦?
- 兩個(gè)不用嵌套的萬(wàn)能提取公式,含用REGEXP函數(shù)提取
- 用Excel制作刷題器,備考無(wú)憂 !
- 如何用LET+LAMBDA直接寫(xiě)遞歸公式?
- 用函數(shù)合并多個(gè)工作表數(shù)據(jù)
- 用LAMBDA自定義顏色求和函數(shù)