Excel中的常用数据清除公式

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,否则将返回1。这将允许我对数据进行排序并删除不完整的记录。

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

修剪和连接字段

如果您的数据具有“姓氏”和“姓氏”字段,但是导入具有“全名”字段,则可以使用内置的Excel Function Concatenate将这些字段整齐地串联在一起,但是请确保使用TRIM删除空格之前或之后的所有空格。文本。 如果其中一个字段没有数据,则使用TRIM包裹整个字段:

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

检查有效的电子邮件地址

一个很简单的公式,它同时查找@和。 在电子邮件地址中:

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

提取名字和姓氏

有时,问题恰恰相反。 您的数据有一个全名字段,但是您需要解析名字和姓氏。 这些公式在名字和姓氏之间寻找空格,并在必要时获取文本。 如果A2中没有姓氏或条目为空白,则IT也会进行处理。

=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)

当然,这些并不意味着全面……只是一些快速公式可以帮助您快速入门! 您发现自己还使用其他哪些公式? 在评论中添加它们,在我更新本文时,我会给你功劳。

你觉得呢?

本网站使用Akismet来减少垃圾邮件。 了解您的数据如何处理.