文章目录
- 前言
- 一、概览-公式汇总
- 1 把多列内容拼接在一起，作为新的一列的内容
- 2 某列的内容是数值，但是格式是字符串形式，需要转换为数值类型，方便计算
- 3 截取指定位置的子串
- 3.1 左截取LEFT、LEFTB
- 3.2 右截取RIGHT、RIGHTB
- 3.3 中间截取 MID MIDB
- 4 分割字符串用得到后的结果形成新的列
- 9 查找字符串
- 9.1 FIND FINDB 区分大小写
- 9.2 SEARCH 和 SEARCHB 不区分大小写
- 二、综合案例
- 2.1 获取指定字符串第n次出现的位置
- 2.2 截取IP地址网段部分
- 2.3获取IP地址后缀
- 2.4 ***截取某个字符串的方法-通用性强***
- 2.5 使用IF语句按需截取字符串
前言
第一章是基础公式的介绍
第二章是实际案例的整理，相当于是公式的现实应用。
一、概览-公式汇总
| 序号 | 公式功能 | 公式 | 公式示例 | 公式说明 |
|---|---|---|---|---|
| 1 | 把多列内容拼接在一起，作为新的一列的内容 | CONCATENATE (text1,text2,…) | =CONCATENATE(A2,“#”,B2,“+”,C2) | 用于根据多个列的内容拼成我们指定格式的内容，拼接的内容通常来源于原始数据，同时有的时候会加上额外固定的内容。 |
| 同上(新版本支持) | =TEXTJOIN(“、”,TRUE,A2:A8) 第1参数是分隔符号，用双引号括起来 第2参数，是否跳过空白单元格 第3参数：表示要连接的文本串 | 字符串拼接新版本的函数，老版本会不支持。如果支持的这个公式的版本使用这个函数做拼接字符串会比较方便。 | ||
| 2 | 某列的内容是数值，但是格式是字符串形式，需要转换为数值类型，方便计算 | VALUE(text) | =VALUE(G2) | 内容是数值，但是在excel中显示的是字符串形式，计算前需要做数据类型转换。 |
| 3 | 截取指定位置的子串 | 左截取 LEFT LEFTB 、右截取 RIGH RIGHB、中间截取MID MIDB | ||
| 4 | 分割字符串用得到后的结果形成新的列 | 用界面的“分列"功能 | ||
| 5 | 计算指定字符串的字符、字节长度 | LEN LENB | ||
| 6 | 文本替换 | REPLACE REPLACEB | ||
| 7 | 指定正则表达抽取相关的内容 | 待补充 | ||
| 8 | 字母大小写转换 | UPPER LOWER | ||
| 9 | 查找字符串 | FIND FINDB 区分大小写；SEARCH SEARCHB不区分大小写 | =FIND(“abc,”,“aabc,ccc”,1) 返回结果：2；– | 函数 FIND 和 FINDB 用于在第二个文本串中定位第一个文本串，并返回第一个文本串的起始位置的值，该值从第二个文本串的第一个字符算起。 |
| 10 | 指定一个字符串，自动生成重复多次的新字符串，用于构造数据使用 | REPT | =REPT(“123”,2) 结果为：123123 | |
| 11 | 在指定的字符串中，使用某个字符串替换指定的字符串，且可以指定替换第几次出现的字符串。 | SUBSTITUTE | =SUBSTITUTE(“a_b_c”,““,”.“) 结果为：a.b.c =SUBSTITUTE(“a_b_c”,””,“.”,2)结果为：a_b.c 最后一个参数2的意识是，只替换第二次出现指定字符串点的位置，其他的保留现状。最后一个参数不指定值的时候，就是全部替换，就如第一个1例子。 | SUBSTITUTE(text,old_text,new_text,instance_num) |
| 12 | 根据某种条件的符合情况来决定如何处理 | IF | =IF(测试条件,真值,[假值]) | =IF(“AA”=“AA”,“等于AA”,“不等于AA”) |
1 把多列内容拼接在一起，作为新的一列的内容
2 某列的内容是数值，但是格式是字符串形式，需要转换为数值类型，方便计算
3 截取指定位置的子串
3.1 左截取LEFT、LEFTB
官方说明：
根据所指定的字符数，LEFT 返回文本字符串中第一个字符或前几个字符。
LEFTB 基于所指定的字节数返回文本字符串中的第一个或前几个字符。
要点
函数 LEFT 面向使用单字节字符集 (SBCS) 的语言，而函数 LEFTB 面向使用双字节字符集 (DBCS) 的语言。您计算机上的默认语言设置对返回值的影响方式如下：
无论默认语言设置如何，函数 LEFT 始终将每个字符（不管是单字节还是双字节）按 1 计数。
当启用支持 DBCS 的语言的编辑并将其设置为默认语言时，函数 LEFTB 会将每个双字节字符按 2 计数，否则，函数 LEFTB 会将每个字符按 1 计数。
=LEFT(“SH上海”,3) 结果为：SH上
=LEFTB(“SH上海”,3) 结果：SH
=LEFTB(“SH上海”,4) 结果：SH上
