目录
1. FIND 位置查找函数
2. SEARCH 字符查找函数
3. LEFT / RIGHT/ MID 字符截取函数
4. CONCATENATE 文本串联函数
5. LEN 计算字符长度函数
6. LENB 计算双字节字符长度函数
7. TRIM 空格清理函数
8. REPLACE 位置替换函数
9. SUBSTITUTE 字符替换函数
10. TEXT 文本转换函数
①按指定格式,将数值转换为文本
②其他文本清洗方法:利用记事本
③将文本转换为数值
11.用查找和替换的注意点
1. FIND 位置查找函数
- =FIND(待查找的字符,选中单元格,开始位置num) 字符记得加引号
- 找出字符串的位置,可查找1个or多个字符。
- 如不填写开始位置,则从选中单元格的第一个字符开始查找
e.g. A1单元格内容为“基础k内容”,需查找A1单元格中字符‘k’的位置
=find(''k'',A1,1) = 3
2. SEARCH 字符查找函数
- =search(待查找的字符串,待查找的单元格,起始位置num)
- 查找一个字符串,在另一个字符串的位置,不区分大小写
e.g. A1单元格内容为“基础k内K容”,需查找A1单元格中第一个出现‘k’的位置
=search("k",A1,1) = 3
3. LEFT / RIGHT/ MID 字符截取函数
① LEFT函数
- =LEFT(选中单元格,截止位置num)
- 从文本字符串的左边第一个字符开始,返回指定个数的字符
- 如未注明截止位置num,则返回左边第1个字符;
- 如注明截止位置num>文本长度,则返回整个文本;
- 如所注明的字符串为0,则返回空值。
e.g. =left(A1,3) 截取A1单元格中第1至3的字符
②RIGHT函数
- 同理,从右边第一个字符开始,返回指定长度的字符。
③MID函数
- =MID(选中单元格,起始位置_num,截止位置num)
- 从指定位置开始,提取用户指定的字符数
引申用法 - 用mid函数随机选取字符
在需要随机选取字符的单元格内,输入以下公式:
=MID(“需选取的字符池”,RANDBETWEEN(1,字符池长度_num),1)
e.g. =MID("男女",RANDBETWEEN(1,2),1)
4. CONCATENATE 文本串联函数
- =CONCATENATE(拼接字符1,拼接字符2,拼接字符3,...)
- 将多个文本字符or单元格串联,显示在同一个单元格内
e.g. A1单元格内容为“基础内容”,A2单元格内容为“打地基”,
=concatenate(A1,A2,"K") = 基础内容打地基K
5. LEN 计算字符长度函数
- =LEN(单元格or文本)
- 返回单元格or字符的长度,即字符数
e.g. A1单元格字符内容为“基础内容”, =LEN(A1) = 4
6. LENB 计算双字节字符长度函数
- =LENB(单元格or文本)
- 同样是返回字符长度,但双字节字符会返回2,单字节返回1
- 汉字及中文标点是双字节;英文字母及英文标点是单字节
e.g. A1单元格字符内容为“基础内容”,
=LENB(A1) = 8
7. TRIM 空格清理函数
- =TRIM(单元格or文本)
- 把前后的空格去掉,但不去除字符之间的空格
8. REPLACE 位置替换函数
- =REPLACE(选中单元格,开始位置num,需替换的字符数num,新字符)
- 字符内容需加双引号,将指定位置的部分字符,替换为新字符
e.g. A1单元格字符内容为“基础内容”,
=REPLACE(A1 , 2 , 2 , "111") = 基111容
9. SUBSTITUTE 字符替换函数
- = substitute( 选中单元格,需替换的文本,新文本,mun_替换第几个)
- 文本记得加双引号
- 对指定的字符串进行替换
- 若有重复字符串,指定为第n个重复字符串,若不填写则每个重复字符串都会被替换
e.g.1 屏蔽手机号码后四位
e.g.2 把A2中的8替换为9
= substitute (A2,"8","9",1)
e.g.3 C1单元格字符内容为“嗯知道了知道了知道了可以”,
= substitute (C1,"知道了","行",1) =嗯行知道了知道了可以
= substitute (C1,"知道了","行") =嗯行行行可以
10. TEXT 文本转换函数
①按指定格式,将数值转换为文本
= TEXT(数值or单元格 , 待转换的值格式) 待转换的值格式需加双引号
e.g. 单元格B1内容为“401” ,单元格B2内容为“2022/3/10”
- =TEXT(B1 , “0.00”) = 401.00
- =TEXT(B2 , “YYYY年M月D日”) =2022年3月14日
②其他文本清洗方法:利用记事本
当日期包含了过多数据,不便于筛选查看。可插入辅助列,利用文本清洗,转换为具体到月份的数据列。以下为例,现需将下列包含具体日期的A列,转换为文本为xx年xx月的格式,以便于后期使用数据透视表和数据筛选。
- 复制该列数据,粘贴为辅助列 首行改为【月份】 → 选中辅助列 - 设置单元格格式 -设为xx年x月的形式 → 复制该列所有数据
- 打开记事本 → 粘贴,如下
- 返回excel,选中该辅助列 → 设置单元格格式 → 将该列全部转为【文本】格式
- 用 Ctrl + A 全选中粘贴在记事本的内容 →复制记事本的文本 → 选中辅助列全列或首行 →粘贴
- 即可把该列全部转为文本格式。可利用辅助列快速筛选
③ 将文本转换为数值
可以使用函数转换,但最简便的方法是分列:
- 数据 - 分列 → 分隔符号(无需操作,默认项)→ 列数据格式 - 常规(无需操作,默认项)→ 完成
11.用查找和替换的注意点
-
如只替换数值内容为0的单元格,为避免替换到所有包含0的单元格,
-
在查找替换时,要设置选项 :
- 点开 选项 → 勾选 单元格匹配
更多推荐
excel数据分析 - 10个清洗文本类函数
发布评论