全文約2500字；

閱讀時間：約7分鐘；

聽完時間：約14分鐘；

在整理員工基本信息檔案時，工廠的人事專員遇到了一個棘手的問題：某一列數(shù)據(jù)混合記載了員工的身份證號碼、銀行卡號、開戶行信息及手機號碼，且這些信息被無序地集中在一個單元格內(nèi)，格式極不規(guī)范。具體而言，身份證號碼間夾雜著空格，部分甚至前端附有不可見字符，還有些數(shù)據(jù)前帶有文本標(biāo)識符“’”。

為應(yīng)對這一挑戰(zhàn)，領(lǐng)導(dǎo)指示專員需將這些混雜的信息拆分并各自獨立成列，即分別列出身份證號、銀行卡號、手機號和開戶行等。然而，鑒于當(dāng)前數(shù)據(jù)格式的不一致性與混亂狀況，手動完成此項任務(wù)極為困難且耗時。因此，迫切需要設(shè)計或采用一種自動化表格處理函數(shù)來有效解決這一問題，確保信息的準(zhǔn)確分離與歸類。

解決思路

盡管面臨的數(shù)據(jù)問題繁多，但經(jīng)過仔細觀察，我們發(fā)現(xiàn)這些數(shù)據(jù)仍存在一定規(guī)律可循。特別是身份證號、銀行卡號以及手機號均以數(shù)字為主，只是其間穿插了一些不規(guī)則字符。針對這一特點，我們可以利用WPS表格中的替換函數(shù)，清除這些不必要的字符，初步凈化數(shù)據(jù)。凈化完成后，即可著手將這些純數(shù)字信息從原單元格中分離，各自置于新的列中。

在進行數(shù)據(jù)分類時，我們可以依據(jù)常見的編號規(guī)則作為指引：身份證號碼固定為18位，銀行卡號多為16、17或19位，手機號碼則一般為11位。依據(jù)這些長度特征，我們可以較為準(zhǔn)確地辨識并區(qū)分出不同類型的數(shù)字串代表的意義。

至于開戶行信息的提取，則更多依賴于文本的識別。一個簡便的方法是查找以“中”字起始、以“行”字結(jié)束的文本段落，這通常能幫助我們定位到開戶行信息。通過這樣的規(guī)則匹配，即使在原始數(shù)據(jù)格式不統(tǒng)一的情況下，也能有效地完成信息的分類與整理工作。

分離數(shù)字

為了幫助大家更清晰地理解函數(shù)的應(yīng)用過程，我將分步驟介紹函數(shù)的使用方法，并說明如何在合適的位置輸入函數(shù)公式并進行填充。以下是一個示例公式：

=REGEXP(B3,'\d.+')

函數(shù)解釋：

\d: 這是一個特殊字符序列，表示匹配任何數(shù)字（0-9）。

.+: 這里的 . 表示匹配任何單個字符（除了換行符），而 + 表示匹配前面的字符一次或多次。

因此，整個正則表達式 '\d.+' 將會匹配任何以數(shù)字開頭的文本，并且會繼續(xù)匹配該數(shù)字之后的所有字符，直到遇到一個換行符

替換空格

在成功提取出數(shù)字后，我們注意到部分數(shù)字間包含空格，需要進一步清理這些空格以確保數(shù)據(jù)準(zhǔn)確性。接下來，通過運用替換函數(shù)達到去空格的目的。請在合適的數(shù)據(jù)范圍內(nèi)輸入并填充以下公式：

=SUBSTITUTE(REGEXP(B3,'\d.+'),' ','')

函數(shù)解釋：

利用SUBSTITUTE函數(shù)，將從REGEXP得到的結(jié)果中所有的空格(' ')替換為無('')(即移除空格)。

信息判斷

把上面的結(jié)果定義為A,，接著，在水平方向上，于C2至E2單元格分別填入標(biāo)題{'身份證', '銀行卡', '手機'}。依據(jù)既定規(guī)則——身份證號碼為18位，銀行卡號常見為16、17或19位，手機號碼通常是11位——我們將在相應(yīng)位置應(yīng)用以下公式并向下填充以匹配每一條記錄：

=IFNA(LET(A,SUBSTITUTE(REGEXP($B3,'\d.+'),' ',''),INDEX(A,,MATCH(C$2,XLOOKUP(LEN(A),{11;16;17;18;19},{'手機';'銀行卡';'銀行卡';'身份證';'銀行卡'}),0))),0)

函數(shù)解釋：

IFNA: 這個函數(shù)用于處理可能出現(xiàn)的錯誤值 #N/A（未找到匹配項時的情況）。如果公式內(nèi)的計算返回了 #N/A 錯誤，IFNA 會替代為指定的值，這里是 0。

LET: 用于定義并命名計算過程中的臨時變量，提高公式的可讀性和效率。這里定義了一個變量 A。

MATCH(C$2,XLOOKUP(...,...,...),0):C$2: 指定的標(biāo)題，比如 '身份證'、'銀行卡' 或 '手機'。

XLOOKUP(LEN(A),{11;16;17;18;19},{'手機';'銀行卡';'銀行卡';'身份證';'銀行卡'}):

LEN(A): 計算數(shù)組 A 中每個數(shù)字序列的長度。{11;16;17;18;19}: 預(yù)定義的一系列長度，對應(yīng)不同類型的號碼。{'手機';'銀行卡';'銀行卡';'身份證';'銀行卡'}: 對應(yīng)長度的類型標(biāo)簽。 注意這里的配置可能需要根據(jù)實際情況調(diào)整，因為銀行卡號長度的重復(fù)可能導(dǎo)致匹配邏輯不夠精確?？梢造`活的增加預(yù)設(shè)值

XLOOKUP 根據(jù)數(shù)字的長度在上述列表中查找，并返回對應(yīng)的類型標(biāo)簽（如 '手機'）。

MATCH(...,0): 使用 MATCH 函數(shù)找到類型標(biāo)簽在數(shù)組 {'手機';'銀行卡';'身份證'} 中的位置，第三個參數(shù) 0 表示完全匹配。

INDEX(A,,...): 根據(jù) MATCH 找到的位置，從數(shù)組 A 中取出對應(yīng)的值。第二個逗號后留空表示取整行，而實際位置由 MATCH 決定。

綜上所述，整個公式旨在根據(jù)數(shù)字的長度自動將其分類并對應(yīng)到“身份證”、“銀行卡”或“手機”等標(biāo)題下，同時處理可能出現(xiàn)的錯誤情況，確保輸出結(jié)果的準(zhǔn)確性。

提取銀行

提取銀行信息的任務(wù)確實可能涉及復(fù)雜性，尤其是在格式不一的數(shù)據(jù)中。若假設(shè)所有銀行名稱都以“*行”作為結(jié)尾標(biāo)識，且該“行”字符后可能跟隨其他信息或直接結(jié)束。錄入以下公式：

=REGEXP(B3,'[ \n\r]+.*?行(?:[^\n\r]*|$)[ \n\r]*')

公式解釋：

這個正則表達式的各部分解釋如下：

[ \n\r]+：開始前匹配一個或多個空格、換行符或回車符。

.*?行：非貪婪地匹配任意字符直到遇到“行”字。

(?:[^\n\r]*|$)：這是一個非捕獲組，表示兩種可能：

[^\n\r]*：匹配任意數(shù)量的非換行符字符，意味著“行”后面可以跟任意文本。

|：或

$：直接到字符串結(jié)束，意味著“行”是字符串的結(jié)尾。

[ \n\r]*：最后匹配任意數(shù)量的空格、換行符或回車符，以適應(yīng)文本末尾可能存在的空白字符。

這樣，無論是文本1中的“中國工商銀行西安金花南路支行”還是文本2中的“建設(shè)銀行醴陵支行”，都能被正確提取出來，不論“行”字后面是否有額外的字符。

最后總結(jié)：

通過上述步驟，我們成功地展示了如何在面對復(fù)雜且不規(guī)范的數(shù)據(jù)格式時，運用一系列精心設(shè)計的公式與邏輯推理，將混雜在單一單元格內(nèi)的員工信息精準(zhǔn)分離并歸類。從最初的識別數(shù)字序列、去除不必要的空格，到依據(jù)特征長度區(qū)分身份證號、銀行卡號與手機號，再到通過正則表達式巧妙提取開戶行信息，這一系列操作不僅體現(xiàn)了數(shù)據(jù)分析的巧思，也彰顯了技術(shù)在解決實際問題中的強大效能。

特別地，對于開戶行信息的提取，我們定制的正則表達式策略，充分考慮了文本多樣性的挑戰(zhàn)，實現(xiàn)了無論“行”字符后是否接續(xù)其他信息，都能準(zhǔn)確捕獲銀行名稱的目標(biāo)。這一過程不僅是對數(shù)據(jù)處理技能的實踐，也是對問題解決思路靈活性的考驗。

總結(jié)而言，本案例不僅解決了工廠人事專員面臨的棘手問題，還為處理類似數(shù)據(jù)分離與清洗任務(wù)提供了寶貴的參考范例。它證明了，即便在數(shù)據(jù)格式不盡人意的情況下，結(jié)合適當(dāng)?shù)墓ぞ吲c方法論，依然能夠高效地挖掘數(shù)據(jù)價值，提升信息管理的規(guī)范性和效率。這種結(jié)合觀察、分析、實施的解決路徑，對于任何需要處理大量數(shù)據(jù)的組織或個人來說，都是一筆寶貴的財富，強調(diào)了在數(shù)字化時代掌握高級數(shù)據(jù)處理技巧的重要性。