二維碼 購物車
部落窩在線教育歡迎您!

提取、查找、替換數(shù)據(jù)的王者——REGEXP正則函數(shù)

?

作者:ITFANS來源:部落窩教育發(fā)布時間:2024-08-06 15:17:29點擊:1288

分享到:
0
收藏    收藏人氣:0人
版權(quán)說明: 原創(chuàng)作品,禁止轉(zhuǎn)載。

編按:

詳細介紹WPS新函數(shù)REGEXP正則函數(shù)的用法。

 

《兩個不用嵌套的萬能提取公式》文章中我們使用了WPS的正則函數(shù)REGEXP。今天為大家詳細介紹這個函數(shù)的強大功能。

 

1.作用與語法

REGEXP函數(shù)根據(jù)正則表達式對字符串進行提取、判斷或替換。

=REGEXP(字符串,正則表達式,[匹配模式],[替換內(nèi)容])

前兩個參數(shù)必選,后兩個參數(shù)則可以根據(jù)實際需要選用。

正則表達式:就是用一些特殊符號表達的字符串規(guī)律。譬如"[0-9]"表示任意單個數(shù)字,"[A-Z]"表示任意大寫單字母。

匹配模式:有三種,0、1、2。0(默認值)表示提取,1表示判斷,2表示替換。

替換內(nèi)容:匹配模式為2時才需要,用于指定替換后的內(nèi)容。若不指定替換內(nèi)容則表示刪除滿足正則表達式中的字符。

 

2.最常用正則表達式字符集

下表簡要介紹幾種正則表達式中最常用的字符集及含義。

 

最常見的字符集

含義

[0-9]

09的數(shù)字字符集

d

等效于[0-9]

d{3}

任意3位數(shù)的字符集。3可以是需要的任何正整數(shù)

[A-Z]

AZ的大寫字母字符集

[a-z]

az的小寫字母字符集

[A-z]

所有大小寫字母字符集

w

0到9和A到z加下劃線_的字符集

[-龜]

常用的漢字字符集

[-]

所有漢字字符集

[-龜]+

任意漢字組成的一段連續(xù)字符集

[A-z]+

任意大小寫字母組成的一段連續(xù)字符集

[0-9.-]+

任意整數(shù)與小數(shù)字符集

[A-z].+

以字母開頭的所有字符集

[一-龜].+

以漢字開頭的所有字符集

[^0-9]

非數(shù)字字符集

D

等效于[^0-9]

[^A-z]

非字母字符集

[^-龜]

非中文字符集

W

非數(shù)字和字母、下劃線_的字符集

 

2. REGEXP基本用法

1)提取模式

提取模式只需使用前兩個參數(shù)。

譬如提取第一個漢字前的所有內(nèi)容:

=REGEXP(A2,"^[^一-龜]+")

 

 

說明:

第一個^,表示從字符串的開頭進行匹配;方括號中的^表示排除,[^-龜]代表非中文字符,[^-龜]+表示至少1個字符長的非中文字符。

 

再如提取第一個漢字起的所有內(nèi)容:

=REGEXP(A2,"[一-龜].+")

 

 

2)判斷模式

第三參數(shù)為1就是判斷模式。

譬如判斷下方型號是否以AB字母開頭的:

=REGEXP(A7,"^AB.",1)

 

 

說明:

.(小圓點),表示任意字符(換行符除外);AB.,表示以AB開頭的任意字符。

 

3)替換模式

第三參數(shù)是2,則進行替換操作。

譬如將數(shù)據(jù)中所有A替換為H。

公式:=REGEXP(A14,"A",2,"H")

 

 

替換模式下,若第四參數(shù)為空,則表示刪除相應(yīng)字符。

 

 

3.典型運用

1)按號段提取手機號

譬如提取屬于移動的手機號。

=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|$)"),"非移動或號碼錯誤")

 

 

說明:

(134|135|136|137|138|139|147|150|151|152|157|158|159|187|188|195|197|198) ,是一個分組, “|”是或邏輯符,用于匹配多個可能的號段。

d{8},d是數(shù)字字符集,{8}表示前面的字符出現(xiàn)8次,也就是號段后出現(xiàn)8位數(shù)字。

以上兩段用于提取符合號段的11位數(shù)字。

(?<=^|D)表示從字符串開頭或者第一個非數(shù)字字符后開始提取。(?<=…),表示某某后的一個位置;D等效于[^0-9]。

(?=D|$),表示提取的手機號位于一個非數(shù)字或字符串末尾之前。(?=…),表示某某前的一個位置;$,表示字符串結(jié)尾。

添加(?<=^|D)和(?=D|$)定位提取位置,確保只能從11位數(shù)字中提取手機號碼。因此表中兩串超過11位的紅色號碼都判定為錯誤。

 

另外,上面的號碼段分組可以用字符集來簡化。

=IFERROR(REGEXP(A2,"(?<=^|D)(13[4-9]|147|15[0-2789]|18[2-478]|19[578])d{8}(?=D|$)"),"非移動或號碼錯誤")

 

 

2)交換數(shù)據(jù)位置

用括號將數(shù)據(jù)分組,然后在替換模式下交換數(shù)據(jù)位置。交換中可增加或刪除內(nèi)容。 

譬如將“001-原封機10G”改成“原封機(舊款)001-10G”。

=REGEXP(A2,"(d{3,4}-)([一-龜]+)",2,"2(舊款)1")

 

 

 

說明

(d{3,4}-),表示將3位或4位數(shù)字加上“-”符號作為分組1。

([一-龜]+),表示將任意字數(shù)的漢字作為分組2。

2(舊款)1,表示將分組2加上字符“(舊款)”并放在分組1之前,從而實現(xiàn)了數(shù)據(jù)位置的交換。

 

3)文本中的數(shù)字運算

可以用REGEXP提取出所有數(shù)字,然后進行運算。

比如下表是產(chǎn)品長、寬、高數(shù)值,現(xiàn)在需要計算體積。

=PRODUCT(--REGEXP(A18,"[d.]+")) 

 

 

 

說明:

[d.]+,表示所有正整數(shù)與小數(shù)。

 

4)復雜數(shù)據(jù)分列

下表需要將系統(tǒng)導出數(shù)據(jù)分成4列,但可用的分隔符只有一個逗號。

 =REGEXP(A2,{"d{3}","[一-龜]+","d{11}","(?<=,)d{4}"})

 

 

 

說明:

REGEXP函數(shù)的第二參數(shù)支持數(shù)組,此處用數(shù)組分別提取實現(xiàn)了分列。

d{3},表示提取3位數(shù)字。此處滿足3位數(shù)字提取的有多個,但在數(shù)組下,只會返回第一個結(jié)果,就是最前面的編號。

(?<=,)d{4},表示從逗號后提取4位數(shù)字。

 

5)多分隔符提取數(shù)據(jù)

直接用非分隔符字符集進行提取。

=REGEXP(A12,"[^, /、]+")

 

 

說明:

數(shù)據(jù)中的分隔符包括英文逗號、空格、斜杠、頓號。[^, /、]+,可以得到除分隔符外的任意字符組合。

 

如果大家對正則表達式的學習有興趣,請留言,若需要的同學較多,我們將安排出教程。

本文配套的練習課件請?zhí)砑涌头⑿?/font>buluowojiaoyu索取。

Excel高手,快速提升工作效率,部落窩教育Excel精品好課任你選擇!

掃下方二維碼關(guān)注公眾號,可隨時隨地學習Excel:

相關(guān)推薦:

多分隔符拆分TEXTSPLIT

批量拆分單元格內(nèi)容變成多行顯示的3種方法

SUMIF錯位求和多表求和與查找

簡單6步完成Excel氣泡地圖

版權(quán)申明:

本文作者ITFANS;部落窩教育享有稿件專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。