首页 > 专题 > excel > 五分钟搞定删除excel单元格空格的问题

五分钟搞定删除excel单元格空格的问题

WBOY
发布: 2022-04-01 20:17:07
转载
17075 人浏览过

本篇文章给大家带来了关于excel的相关知识,其中主要介绍了删除单元格空格的相关问题,删除单元格中的空格。这个问题看似简单,但其实有点儿复杂。它大致可以分为四种类型的小问题,下面一起来看一下,希望对大家有帮助。

五分钟搞定删除excel单元格空格的问题

相关学习推荐:excel教程

今天给大家聊一下数据清理整理过程中比较常见的一个问题:删除单元格中的空格。这个问题看似简单,但其实有点儿复杂。它大致可以分为四种类型的小问题,接下来,咱们由浅入深,一个一个聊。

1、 正经的空格删除

先说第1种最简单的情况。

如下图所示,A:B是数据源,A列是人名,B列是成绩。由于A列人名的前后包含了大量空格,导致E列的VLOOKUP函数返回错误值。

08.png

对于这种情况,直接查找替换,将空格替换为空白即可。

09.gif

需要注意的是,这里的空格,最好是从单元格中复制一个,而不是手动输入。后文你会了解到,空格有几十上百种风情,而空格键键入的只是其中普通的一种~

2、身份证中的空格

说一种比较特殊的情况,删除身份证中的空格。

如下图所示,A列身份证号中存在空格,需要删除。

10.png

有朋友第一反应是查找替换,但是由于身份证属于长文本,替换后会被转换为数值,而单元格有效保存的数值最大长度是15位,这就导致18位的身份证的后3位数值会被转换为0.

11.gif

常用的解法方法有两个,一个是SUBSTITUTE函数,文本函数返回的结果必然是文本,所以不会导致身份证号变形:

=SUBSTITUTE(A2,” “,””)
登录后复制

另外一个还是查找替换,只不过增加了一点前戏,用格式刷强制单元格转换为文本格式。

12.gif

3、去除首尾空格

有时候我们不需要把数据中全部的空格都删除掉,而需要删除首尾的全部空格,中间连续的空格保留一个,对此Excel提供了一个专门的函数:TRIM。

如下图所示,A列的数据包含了大量的空格,需要转换为B列的样式。

+1.png

B2单元格输入以下公式即可:

=TRIM(A2)
登录后复制

4、删除系统导出的空格

上文我们说过,空格有成百上千种,空格键键入的只是其中普通的一种。

你在A2单元格输入公式:

=UNICHAR(ROW(A1))
登录后复制

将它填充到A1:A10000区域,就可以见到各种各样的字符图形,牛啊羊啊飞机啊大炮啊轮船啊汉堡啊等等,也有各种各样的可见不可见的空格。

飞机大炮要啥有啥,

如果你有闲,也可以用这些图形作画儿…

13.png

从系统导出的数据,有时候包含的空格,就不是正经的空格键生下来的。

对于这种,如果是可见的,你可以从中复制一个,然后查找替换。

如果查找替换失败了,可以换用TRIM+CLEAN的函数组合:

=CLEAN(TRIM(A1))
登录后复制

CLEAN,在英语中是清洁打扫的意思,它可以清理掉一部分不可见的空格。

但不管是查找替换,还是CLEAN函数,都是Excel近古时代开发出的功能了,这就导致它们解决不了很多新生代的空格。

比如,著名的零宽空白8203。8203是它的UNICODE编码,如果你的Excel版本是2019及以上,可以使用UNICHAR(8203)返回该字符。

零宽空白8203就像一个幽灵,完全不可见,不但Excel不可见,数据复制到写字板、Word等软件也不可见,但它真实存在的不要不要的,一样会造成VLOOKUP等条件查询或统计函数无法正确计算。

如下图所示,使用LEN函数,可以发现该函数返回的字符串长度和肉眼所见完全不同,但在编辑栏里你又发现不了任何多余的赘肉字。

14.gif

对于这种情况,由于不可见字符通常出现在数据的首尾,可以使用LEFT函数查找首个字符是否返回空白。

15.png

如果LEFT函数返回结果为空白,则使用SUBSTITUTE函数将它替换即可。

=SUBSTITUTE(A2,LEFT($A$2),””)
登录后复制

16.png

同理,如果空格在尾部,可以使用RIGHT函数:

=SUBSTITUTE(A2,RIGHT($A$2),””)
登录后复制

或者管它是头是尾是左是右是男是女,二元对立多烦啊?统统一刀切了!

代码看不全可以左右拖动..

=SUBSTITUTE(SUBSTITUTE(A2,RIGHT($A$2),””),LEFT($A$2),””)
登录后复制

相关学习推荐:excel教程

以上是五分钟搞定删除excel单元格空格的问题的详细内容。更多信息请关注PHP中文网其他相关文章!

相关标签:
来源:excelhome.net
本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板