視頻鏈接
一、使用文本截取字符串
1.Left函數
LEFT(字符串,[字符個數])
從一個文本字符串的第一個字符開始返回指定個數的字符
獲取“綽號”
2.Right函數
RIGHT(字符串,[字符個數])
從一個文本字符串的最後一個字符開始返回指定個數的字符
獲取“後4位編碼”
身份證提取性別位數字(Right+Left)
15位身份證最後一個代表性別,18位倒數第二位代表性別,所以要判斷身份證上性別必須截取17位。
對於15位身份證,截取17位就是它的全部數字(與取姓名相似)
=RIGHT(LEFT(B13,17),1)
3.Mid函數
MID(字符串,開始位置,字符個數)
從文本字符串中指定的位置開始,返回指定長度的字符串
(1)獲取“第3、4、5位編碼”
(2)獲取“姓名”
我們可以根據“取多不取少原則”來確定姓名的字符個數。在這種情況下,我們會從第4位開始多給幾位字符,以確保不會漏掉任何可能的姓名。
二、獲取文本中的信息
1.Find函數
FIND(要查找的字符串,被查找字符串,[開始位置])
返回一個字符串在另一個字符串中出現的起始位置。(區分大小寫,且不允許使用通配符)
開始位置可選參數,指定開始查找的位置(默認為1,即從第一個字符開始查找)
(1)通過郵箱地址查找用戶名和域名
用戶名:在“@”的左邊,所以我們要知道“@”在字符串中位數,位數-1為用戶名長度
=LEFT(郵箱地址,FIND("@",郵箱地址)-1)
域名:在“@”右邊部分,但是右邊字符串長度不一致,所以採用Mid函數多取幾位字符
=MID(郵箱地址,FIND("@",郵箱地址)+1,100)
2.Len與Lenb函數
(1)LEN 函數
功能:返回字符串中的字符數(以字符為單位)。
適用場景:主要用於處理單字節字符(如英文字母、數字)以及多字節字符(如中文、日文、韓文等),每個字符都被視為一個單位。
語法:LEN(text)
text:要計算長度的字符串。
示例:對於字符串 "Hello你好",=LEN("Hello你好") 返回 7,因為有 5 個英文字母和 2 個漢字,每個漢字也算作一個字符。
(2)LENB 函數
功能:返回字符串的字節數(以字節為單位)。
適用場景:在需要考慮字節長度的場合(如某些編碼規則)使用。對雙字節字符(如中文、日文、韓文等)進行特殊處理,每個雙字節字符佔 2 個字節,而單字節字符(如英文字符、數字等)佔 1 個字節。
語法:LENB(text)
text:要計算字節數的字符串。
注意事項:
在需要雙字節字符支持的語言環境中(如中文或日文版Excel),LENB 會將雙字節字符按 2 字節計算。
如果在不支持雙字節語言的環境中,LENB 的結果可能與 LEN 一樣。
示例:
對於字符串 "Hello你好",=LENB("Hello你好") 返回 11,因為 Hello 佔 5 個字節,每個漢字佔 2 個字節(共 4 個字節),總計 5 + 4 = 9。
(3)示例:獲取數據中的單位
方法:中文為雙字節字符,283元的字符長為4,字節長為5;7852千克字符長為6,字節長為8.那麼我們可以知道lenb-len的長度為單位字符長度,公式 =RIGHT(A2, LENB(A2) - LEN(A2))
總結
在實際使用中,如果只需要字符個數,用 LEN;如果需要字節長度(如編碼限制等場景),用 LENB。
三、關於身份證
1.身份證號碼規則
(1)地區代碼(前6位):
前6位數字表示身份證的地區編碼,可以根據行政區劃表查詢所屬地區。
(2)出生日期(第7-14位):
這8位數字表示出生年份、月份和日期(如19760517表示1976年5月17日)。
(3)性別(第17位):
第17位數字表示性別,奇數為男性,偶數為女性。
(4)校驗碼(第18位):
第18位是校驗碼,用於驗證身份證號碼的合法性。校驗碼可以是數字或字母“X”,通過一系列計算公式得出。
2.示例
(1)地區代碼(前6位):
公式:
=LEFT(B2, 6)
根據提取的地區代碼,可以通過行政區劃表對照查詢地區名稱。
(2)出生日期(第7-14位):
公式:
=MID(B2, 7, 8)
將返回格式為 YYYYMMDD 的日期,比如 19760517。
如果想轉換為標準日期格式,可以使用:
=DATE(LEFT(MID(B2, 7, 8), 4), MID(MID(B2, 7, 8), 5, 2), RIGHT(MID(B2, 7, 8), 2))
(3)性別(第17位):
公式:
IF(MOD(MID(B2, 17, 1), 2), "男", "女")
MOD(MID(B2, 17, 1), 2) 判斷第17位數字是奇數還是偶數,奇數為男性,偶數為女性。
(4)校驗碼(第18位):
校驗碼的計算規則
校驗碼由以下公式計算:
步驟 1:確定加權因子
每一位數字都有一個對應的加權因子:
加權因子 = {7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2}
對應位置的數字和加權因子相乘後加總。
步驟 2:計算加權和
取身份證號碼前17位,與加權因子一一對應相乘,並求和:
加權和 = (第1位 × 7) + (第2位 × 9) + (第3位 × 10) + ... + (第17位 × 2)
步驟 3:計算校驗碼索引
對加權和取模(%)11,結果是一個 0 到 10 之間的數字:
校驗碼索引 = 加權和 % 11
步驟 4:匹配校驗碼
根據索引值查表得到校驗碼:
校驗碼可以是數字 0-9 或字母 X(表示10)。
步驟 5:比對校驗碼
將計算得到的校驗碼與身份證號碼的第18位對比,如果一致,則身份證號碼合法;否則為非法。
公式
=MOD(SUM(MID(B2, {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}, 1) * {7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2}), 11)
與效驗碼對比即可