Excel数据清洗之五 去除多余的字符

今天介绍数据清洗需要处理的第二类问题,分类要一致。

Excel数据清洗之五 去除多余的字符

我们先看分类不一致的结果,

Excel数据清洗之五 去除多余的字符

上图中,很明显我们看到一个编号为“CA1001101”的客户,但是分别被归类到不同的客户中。

造成这种情况的原因就是因为这个编号在源数据中有各种表现形式,

Excel数据清洗之五 去除多余的字符

今天介绍的方法就是要解决源数据中文本内容一致性的问题。

分类数据不一致,有很多原因,我们先来看其中的3个常见情形,

  • 多余的前后缀

  • 多余的空格

  • 多余的回车(换行)

对于这几种情况,我们用的最多的就是“查找和替换”,在某些特殊情况下,我们还需要使用函数来处理。

Excel数据清洗之五 去除多余的字符

场景1 去掉多余的前缀(后缀)

一般来说,我们首先查找确定是否需要进行替换操作。例如:

确定某一列中是否包含一些特定的文本。比如,我们知道某个大区上传的数据总是会在客户编号中添加一些特定的前缀。我们就可以使用熟悉的“查找和替换”工具

Excel数据清洗之五 去除多余的字符

如果发现有这些不必要的字符,就可以点击替换按钮,将这些文本替换为空:

Excel数据清洗之五 去除多余的字符

点击“全部替换”即可。

Excel数据清洗之五 去除多余的字符

场景2 去掉多余的空格

为了保证某列(或者某几列)中没有多余的空格,例如“回归线   ”中后面就有多余的空格,一般来说,这些空格是不必要的。

Excel数据清洗之五 去除多余的字符

需要提醒的是,英文的空格和中文的空格是两个不同的字符,为了保证完全清除多余的空格,你应该执行两次分别清除英文空格和中文空格。

另外,这个方法用在英文文本中的时候,需要注意。因为英文中是通过空格来分隔单词的。如果你对英文文本使用了这个方法,就可能将一段这样的英文:

Move one cell to the right in a worksheet. Or, in a protected worksheet, move between unlocked cells.

处理为:

Moveonecelltotherightinaworksheet.Or,inaprotectedworksheet,movebetweenunlockedcells.

这显然不是我们的初衷(对于英文的空格处理,见本文下面的场景4)

Excel数据清洗之五 去除多余的字符

场景3 去掉多余的回车(换行)

有时,源数据中会包含多余的回车,

Excel数据清洗之五 去除多余的字符

这里的分行是通过输入Alt+Enter来实现的。我们可以将这里的回车替换掉:

Excel数据清洗之五 去除多余的字符

要注意的是在替换对话框中,“查找内容”框中需要使用Ctrl+Enter输入回车。如果不行,就是用Ctrl+j来输入回车。

Excel数据清洗之五 去除多余的字符

场景4 去掉英文文本中的多余空格

英文是通过空格来分割单词的。例如,"   Tropic Excel   "这个文本中,有3处空格,分别是T前面的空格,两个单词之间的空格,和l后面的空格。这里两个单词之间的空格是必要的,不能去掉。而开头和结尾处的空格是多余的,一般应该去掉。这里用替换工具行不通,我们可以使用一个函数,TRIM:

Excel数据清洗之五 去除多余的字符

当然,这个方法也可以用来去掉中文文本中的多余空格。

有时候,这个空格的处理还比较麻烦,有些空格不能用这个方法去掉,我们就需要使用其他方法了。下一讲,我们在讲处理不可见字符时还会为大家介绍一种“空格”的处理。

Excel数据清洗之五 去除多余的字符

总结

源数据中的“分类不统一”的问题非常常见,如果没有进行这个数据清洗的步骤,在后续的工作中必然面临很多麻烦。例如,进行VLOOKUP匹配时找不到正确结果;数据透视时产生了多余的分类;甚至没有办法使用数据透视进行汇总分析,只能绞尽脑汁写复杂的公式甚至VBA代码。

另一方面,查找和替换是一个几乎所有人都熟悉的工具,只要利用它对数据进行一点简单的处理,就可以在后续工作中减少很多不必要的麻烦。

Excel数据清洗之五 去除多余的字符

2021-06-17

Excel数据清洗之五 去除多余的字符的相关文章

【在职场中学Excel】FIND函数查找的字符重复,如何对条件进行判断?

Excel情报局 Excel职场联盟 生产挖掘分享Excel基础技能 Excel爱好者大本营 用1%的Excel基础搞定99%的职场问题 做一个超级实用的Excel公众号 Excel是门手艺玩转需要勇 ...

Excel数据清洗实例智能填充应用

Excel数据清洗实例智能填充应用

Excel中的63号字符,你不会,活该你加班

63号字符是啥,这个问题说来话长,所以咱就长话短说. 在计算机中,每个字符都有对应的数字代码表示.可以用char()函数获取数字所表示的字符,或者用code()函数获取字符对应的代码. 但是,code ...

Excel中如何提取特定字符?

您好!我是EXCEL学习微课堂,头条教育视频原创作者,分享EXCEL学习的小技巧,小经验. Excel中如何提取特定字符?比如提取数字.中文.英文等,下面介绍两种简单实用的方法. 第一种方法是用快速填 ...

Excel单元格里面提取或去掉某些字符

Excel单元格里面提取或去掉某些字符

Excel包含指定字符自动标注颜色,所有曹姓员工自动标黄!

工作中,我们经常需要找出包含指定内容的信息,比如:某个姓氏的员工,某种产品的品名,某个公司的简称等等,使用筛选需要进行大量重复操作,有没有什么方法,可以自动标出所有信息呢?今天,技巧君就来分享一下,如 ...

不管什么手机,教你一键去除照片里多余的人物,让照片更加完美

不管什么手机,教你一键去除照片里多余的人物,让照片更加完美

将内容按字符出现次数排列?这个在Excel里的难题因为PQ而轻松解决!

比如单元格里的内容是11124533,按各个数字出现的次数多少进行排列,结果表示为:13245. 这个问题如果通过Excel的传统功能或函数来实现,会比较困难,而使用Power Query却简单很多. ...

删除Excel表格中多余空白行的4种方法,你会几个?

表格中难免会出现了一些多余的空白行/空白列/空白单元格,怎样快速删除这些空白呢?至少有4种方法,看看你都会几个 一.排序法 1.行与行之间没有顺序 那么强烈推荐你重新给数据按照数值升序排列,排列之后所 ...

Excel函数套路提取( )之间字符

内容提取:mid+left+find函数 指定字符之间数据提取:mid+left+find+substitute函数