您当前的位置:首页 > 文本与office

Excel数据源清洗,用这两个函数批量删除空格和换行

时间:08-31来源:作者:点击数:

Excel 使用久了的话,大家会发现日常遇到的问题大都就是那几类。

比如,最常见的问的症结就是数据源不规范,如果不从源头清洗干净,后续不管是公式、图表,还是数据透视表,都会出现错误。

清洗数据这件事情,如果全凭眼力和手工调整,那工作量会很惊人,所以我们需要掌握一些批量处理不同数据格式问题的方法。

不久前我曾教过大家如何规范日期格式,具体可参见 Excel – 将各种伪日期批量转化为真日期

今天,我要教大家用两个函数解决另外两大痛点:

  • 删除单元格内多余空格
  • 删除单元格内多余换行

案例:

下面两张图中,单元格中存在垃圾空格或换行,以至数据透视表结果不准确。请批量清除垃圾字符。

解决方案 1:清除空格

对图 1 拉个数据透视表,就可以清楚地看到问题出在单元格中存在多余空格。删除空格需要用到 trim 函数。

语法:

TRIM(text)

  • Text:必需;要从中移除空格的文本

作用:

删除文本中的所有空格,单词之间正常的单个空格除外

1. 在 B2 单元格输入以下公式,下拉整列复制公式:

=trim(A2)

2. 复制 B2:B6 区域 --> 选中 A2:A6 区域 --> 选择性粘贴为数值

3. 肉眼可见 A 列数据的多余空格已经去除了,为了检验真伪,我们选中数据透视表的任意数据 --> 右键单击 --> 选择 Refresh(刷新)

4. 空格确实已经批量删除

解决方案 2:清除换行

图 2 的数据问题是单元格内有一个或多个换行,对于这种情况,需要用另一个函数 clean

语法:

CLEAN(text)

  • Text:必需; 要从中删除非打印字符的任何工作表信息

作用:

删除文本中所有不能打印的字符。

那么到底删除了哪些字符呢?微软官方是这么解释的:CLEAN 函数用于删除文本中 7 位 ASCII 码的前 32 个非打印字符(值为 0 到 31)。 在 Unicode 字符集中,有附加的非打印字符(值为 127、129、141、143、144 和 157)

为了便于大家理解,我在下表中列举了ASCII 码的前 32 个非打印字符,供参考:

现在回到案例,开始解决步骤:

1. 在 B2 单元格输入以下公式,下拉整列复制公式:

=clean(A2)

2. 复制 B2:B6 区域 --> 选中 A2:A6 区域 --> 选择性粘贴为数值

3. 刷新数据透视表检验结果,正确无误

方便获取更多学习、工作、生活信息请关注本站微信公众号城东书院 微信服务号城东书院 微信订阅号
推荐内容
相关内容
栏目更新
栏目热门