excel数据分析 - 10个清洗文本类函数

目录

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的单元格，

• 在查找替换时，要设置选项 ：

• 

• 点开  选项 → 勾选   单元格匹配