excel函数trim用法,用这两个函数批量删除空格和换行
关于【excel函数trim用法】,今天犇涌小编给您分享一下,如果对您有所帮助别忘了关注本站哦。
1、excel函数trim用法:Excel 数据源清洗,用这两个函数批量删除空格和换行
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. 刷新数据透视表检验结果,正确无误
2、excel函数trim用法,textjion函数简直太好用啦
第一步:需要计算每个家庭的成员数- 方法1:在J2中输入公式
=IF(D2="户主",COUNTA(B2:B72)-SUM(J3:J72),"")
判断d2是否为户主,统计B2:B72区域的非空单元格与J3:J72区域的汇总之差即是每个家庭成员人数。
- 方法2:在H2中输入公式
=IF(D2="户主",MATCH("户主",D3:$D$72,0),"")
判断d2是否为户主,确定户主在D3:$D$72的位置,每次下拉公式需要注意的是该区域的变化,起点d3是变化的,但是match函数只会确定户主第一次出现的位置。
第二步:合并所有人员,在I2中输入公式=IF(D2="户主",TEXTJOIN(" ",,OFFSET(B2,,,J2)),"")
- 公式解释:首先判断D2中是否为户主
满足条件的话就将户主下的所有人员合并,否则就填充空值
TEXTJOIN函数语法=TEXTJOIN(分隔符,1/0,合并范围)
- 第一参数:分隔符使用的一个空格,可以根据自己的需要选择
- 其中第二参数:1是代表忽略空单元格,当为0或者省略的时候即包含空单元格
当然也可将1改为true,0改为false
- 第三参数合并范围:使用一个offset函数最简单的用法即OFFSET(B2,,,J2)动态图解:3分钟深入了解极品函数offset
这里的向下/右偏移的行数/列数量为0,即是以B2为基点,引用一个新的区域行数为j2中的数值,j2即统计的所有家庭成员数,比如第一户赵国朝,引用的区域即为3行1列的数据
这就是关于《excel函数trim用法,用这两个函数批量删除空格和换行》的所有内容,希望对您能有所帮助!更多的知识请继续关注《犇涌向乾》百科知识网站:http://www.029ztxx.com!
版权声明: 本站仅提供信息存储空间服务,旨在传递更多信息,不拥有所有权,不承担相关法律责任,不代表本网赞同其观点和对其真实性负责。如因作品内容、版权和其它问题需要同本网联系的,请发送邮件至 举报,一经查实,本站将立刻删除。