多年來,我一直使用該出版物作為描述如何做事的資源,並保留記錄以供自己以後查找! 一位客戶向我們提供了一個災難性的客戶資料檔。 幾乎每個欄位的格式都會錯誤,因此我們無法匯入資料。 雖然 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)
當然,這些並不意味著全面……只是一些幫助您快速入門的快速公式! 您發現自己還使用哪些其他公式? 將它們添加到評論中,我會在更新本文時給予您好評。