CRM和數據平台營銷工具

用於常見數據清理的 Excel 公式

多年來,我一直使用該出版物作為描述如何做事的資源,並保留記錄以供自己以後查找! 一位客戶向我們提供了一個災難性的客戶資料檔。 幾乎每個欄位的格式都會錯誤,因此我們無法匯入資料。 雖然 Excel 有一些很棒的附加元件可以使用 Visual Basic 進行清理,但我們運行的是 Office for Mac,它不支援巨集。 相反,我們尋找直接的公式來提供幫助。 我想我應該在這裡分享其中一些,以便您可以使用它們。

刪除非數字字符

系統通常要求以特定的 11 位元公式插入電話號碼,並帶有國家/地區代碼且不包含標點符號。 然而,人們經常用破折號和句點輸入這些資料。 這是一個很好的公式 刪除所有非數字字符 在Excel中。 該公式將檢查單元格A2中的數據:

=IF(A2="","",SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))

您可以複製結果列並使用 編輯>粘貼值 用正確格式的結果覆蓋數據。

使用OR評估多個字段

我們經常從導入中清除不完整的記錄。 使用者沒有意識到您不必總是編寫複雜的層次結構公式,而是可以編寫 OR 語句。 我想檢查下例中 A2、B2、C2、D2 或 E2 是否缺少資料。 如果缺少任何數據,我將返回 0; 否則,a 1。這將允許我對資料進行排序並刪除不完整的記錄。

=IF(OR(A2="",B2="",C2="",D2="",E2=""),0,1)

修剪和連接字段

If your data has First and Last Name fields, but your import has a full name field, you can concatenate the fields together neatly using the built-in Excel Function Concatenate, but be sure to use TRIM to remove any empty spaces before or after the文字. 如果其中一個欄位沒有數據,我們用 TRIM 包裝整個欄位:

=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))

檢查有效的電子郵件地址

一個非常簡單的公式,可以找到 @ 和 . 在電子郵件地址(不是 RFC標準

):

=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))

提取名字和姓氏

有時,問題恰恰相反。 您的資料有一個全名字段,但您需要解析出名字和姓氏。 這些公式會尋找名字和姓氏之間的空格,並在必要時抓取文字。 它還會處理 A2 中沒有姓氏或空白條目的情況。

=IFERROR(IF(SEARCH(" ",A2,1),LEFT(A2, SEARCH(" ",A2,1)),A2),IF(LEN(A2)>0,A2,""))

還有姓:

=IFERROR(IF(SEARCH(" ",A2,1),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)),A2),"")

限製字符數並添加…

您是否曾經想清理您的元描述? 如果您想要將內容提取到 Excel 中,然後修剪內容以用於元描述欄位(150 到 160 個字元),您可以使用此公式來執行此操作。 它乾淨地在空格處打破了描述,然後添加了…:

=IF(LEN(A1)>155,LEFT(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ",""))))) & IF(LEN(A1)>FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ","")))),"…",""),A1)

當然,這些並不意味著全面……只是一些幫助您快速入門的快速公式! 您發現自己還使用哪些其他公式? 將它們添加到評論中,我會在更新本文時給予您好評。

Douglas Karr

Douglas Karr 是 CMO 的 開放洞察 和創始人 Martech Zone。 道格拉斯幫助了數十家成功的 MarTech 新創公司,協助進行了超過 5 億美元的 MarTech 收購和投資盡職調查,並繼續協助公司實施和自動化其銷售和行銷策略。 道格拉斯是國際公認的數位轉型和 MarTech 專家和演講者。 道格拉斯也是一本傻瓜指南和一本商業領導書的出版作者。

相關文章

返回頂部按鈕
關閉

檢測到Adblock

Martech Zone 我們能夠免費為您提供這些內容,因為我們通過廣告收入、聯屬鏈接和讚助從我們的網站中獲利。 如果您在瀏覽我們的網站時刪除廣告攔截器,我們將不勝感激。