工作中,我們經常需要從成千上萬條資料中查詢和替換某些字元,如果一個一個查詢,不僅效率低下,而且容易漏查錯查。
以下4個查詢和替換函式,將會助力你快速定位,查詢和替換,效率槓槓滴!
1、
FIND函式
如下圖,根據A列的省市名稱,分別提取“省”和“市”的資訊。
提取"省份"資訊:
=LEFT(A2,FIND(“-”,A2)-1)
LEFT(在哪裡找,找幾位)
LEFT(A2,2),從A2字串左邊找起,取兩位,結果為“陝西”。
FIND(在哪裡找,找什麼)
FIND(“-”,A2):在A2字串中,找“-”字元,可以看到“-”處在A2字串的第3位,所以查詢結果為3。
=LEFT(A2,FIND("-",A2)-1)
表示從A2字串中,先找到“-”的位置,然後減去1,就是所需提取的“省”份的字元個數。再使用LEFT函式左起取出這幾位即可。
提取"市"資訊:
=RIGHT(A2,LEN(A2)-FIND(“-”,A2))
RIGHT(從哪裡找,找幾位)
RIGHT(A2,2):從A2字串中,取右邊的兩位。
LEN(A2):A2字串的字元數。
FIND("-",A2):
“-”在A2字串中的位置。
LEN(A2)-FIND("-",A2)
為從右邊起,需要提取的“市”的字元數。
=RIGHT(A2,LEN(A2)-FIND("-",A2)):
從右邊起,提取2位字元。
2、
SEARCH函式
SEARCH和FIND函式大多數情況下可以通用,他們倆的區別主要有兩點:
區別1:
FIND查詢時,區分大小寫。比如“A”和“a”在FIND進行查詢時,是不一樣的。但是SEARCH查詢時,當做同一字元處理。
區別2:
SEARCH可以使用萬用字元進行查詢。FIND不支援萬用字元查詢。
如下圖:
1、 從A列的資料中,找到“wu”(不區分大小寫)所在位置,並返回結果值。如果找不到,則返回“找不到”。
2、 從A列資料中,找到李姓,名為2個字元的姓名所在位置,並返回結果值。如果找不到,則返回“找不到”。
找“wu”的位置,不區分大小寫,所以需要使用查詢函式“SEARCH”。
找不到“wu”時,返回“找不到”,需要用到IFERROR函式。
=IFERROR(SEARCH("wu",A2),"找不到")
SEARCH(“wu”,A2):在A2單元格的字串中查詢“wu”的位置。
IFERROR(公式,自定義值)。
一般IFERROR的第一個引數是一個公式,如果公式結果正常,則返回公式結果。如果公式結果是個錯誤值,可以在第二個引數中進行自定義。
比如:
=IFERROR(SEARCH("wu",A2),"找不到")
當SEARCH結果正常時,返回對應的位置值,當找不到時,excel預設返回“#VALUE!”,顯得不人性化,所以可以使用IFERROR函式,當找不到時,直接返回“找不到”就可以了。
問題2分析:
找到李姓,名為2個字元的姓名所在位置。這裡需要用到萬用字元“李??”查詢,所以使用SEARCH函式。
找不到“李??”時,返回“找不到”,需要用到IFERROR函式。
=IFERROR(SEARCH("李??",A2),"找不到")
SEARCH(“李??”,A2):找到李姓,名字為兩個字元的人名。
萬用字元:“*”代表任意字元,“?”一個問號代表一個字元。
=IFERROR(SEARCH("李??",A2),"找不到")
表示找到“李??”時,返回他的位置,否則,返回“找不到”。
3、
REPLACE函式
將下圖中的身份證號碼中間8位出生年月日用“****”進行替代。
=REPLACE(B2,7,8,"****")
B2字串,從第7位開始的8個字元,用“****”替代。
引數1:進行處理的字串。
引數2:起始字元位置。
引數3:字元個數。
引數4:替換成的新字元。
4、
SUBSTITUTE函式
SUBSTITUTE語法如下:
SUBSTITUTE(原字串,替換的原字元,新字元,替換序號)
引數1:原字串。比如“abcabcabc”。
引數2:需要替換的原字元。比如“ab”。
引數3:替換的新字元。比如“AB”,表示將“ab”替換為“AB”。
引數4:可選引數。如果省略,表示將原字串中的所有指定字元進行替換,結果為“ABcABcABc”。
如果不省略,假設替換序號為2,代表將原字串中的第二個指定字元替換為新字元,結果為“abcABcabc”。
剛才,我們用REPLACE函式將下圖中的身份證號碼中間8位出生年月日用“****”進行替代。現在,使用SUBSTITUTE來進行替換。
公式如下:
=SUBSTITUTE(B2,MID(B2,7,8),"****")
MID(B2,7,8):B2字串,從第7位開始,取8個字元,結果為“19630405”。
=SUBSTITUTE(B2,MID(B2,7,8),“****”):將B2字串的中間8位字元,替換為“****”。
一般情況下,REPLACE可以實現的功能,SUBSTITUTE都可以實現。但是SUBSTITUTE能夠實現的功能,REPLACE不一定能實現,例如,將下表身份證號碼中的6替換為“*”。
=SUBSTITUTE(B2,"6","*")
如果要將上表身份證號碼中的第二個“6”替換為“*”,使用下述公式:
=SUBSTITUTE(B2,"6","*",2)
結束語:
親愛的小夥伴,如果你喜歡,請
關注,點贊
,
轉發
哦!