Excel 文本查找与替换 #

函数概览 #

文本查找与替换函数用于在文本中定位特定内容并进行替换操作，是处理复杂文本数据的重要工具。

text

┌─────────────────────────────────────────────────────────────┐
│                    文本查找函数一览                          │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  FIND      查找位置    =FIND("l", "hello")      → 3         │
│  SEARCH    查找位置    =SEARCH("L", "hello")    → 3         │
│  REPLACE   替换文本    =REPLACE("hello",1,2,"HI") → "HIllo" │
│  SUBSTITUTE 替换文本   =SUBSTITUTE("hello","l","L") → "heLLo"│
│                                                             │
└─────────────────────────────────────────────────────────────┘

FIND 函数 #

语法 #

excel

=FIND(find_text, within_text, [start_num])

参数：
- find_text: 要查找的文本
- within_text: 在哪个文本中查找
- start_num: 开始查找的位置（可选，默认为1）

返回：找到的位置（从1开始），找不到返回 #VALUE!

基本用法 #

excel

=FIND("l", "hello")           → 3     第一个"l"的位置
=FIND("l", "hello", 4)        → 4     从第4位开始找"l"
=FIND("lo", "hello")          → 4     "lo"开始的位置
=FIND("x", "hello")           → #VALUE!  找不到

大小写敏感 #

excel

=FIND("L", "hello")           → #VALUE!  区分大小写
=FIND("l", "hello")           → 3
=FIND("L", "HELLO")           → 3

实际应用 #

excel

提取@前的用户名：
=LEFT(A1, FIND("@", A1)-1)

提取@后的域名：
=RIGHT(A1, LEN(A1)-FIND("@", A1))

提取第一个空格前的词：
=LEFT(A1, FIND(" ", A1)-1)

实际示例 #

text

邮箱处理：
    A                     B              C
1   邮箱                  用户名         域名
2   zhang@example.com     =LEFT(A2, FIND("@", A2)-1)  → "zhang"
                          =RIGHT(A2, LEN(A2)-FIND("@", A2))  → "example.com"

查找第N次出现的位置 #

excel

查找第二个"l"的位置：
=FIND("l", "hello", FIND("l", "hello")+1)  → 4

查找第三个"l"的位置：
=FIND("l", "hello world", FIND("l", "hello world", FIND("l", "hello world")+1)+1)

SEARCH 函数 #

语法 #

excel

=SEARCH(find_text, within_text, [start_num])

与 FIND 类似，但不区分大小写，支持通配符

基本用法 #

excel

=SEARCH("l", "hello")         → 3
=SEARCH("L", "hello")         → 3     不区分大小写
=SEARCH("l", "HELLO")         → 3     不区分大小写
=SEARCH("l?o", "hello")       → 3     支持通配符
=SEARCH("x", "hello")         → #VALUE!

通配符支持 #

excel

? - 匹配任意单个字符
* - 匹配任意多个字符

=SEARCH("l?o", "hello")       → 3     匹配 "llo"
=SEARCH("h*o", "hello")       → 1     匹配 "hello"
=SEARCH("?e?", "hello")       → 1     匹配 "hel"

FIND vs SEARCH #

特性	FIND	SEARCH
大小写敏感	是	否
通配符支持	否	是
返回值	位置	位置
找不到时	#VALUE!	#VALUE!

实际应用 #

excel

不区分大小写查找：
=SEARCH("excel", "Learn EXCEL functions")  → 7

检查是否包含某文本：
=IF(ISNUMBER(SEARCH("excel", A1)), "包含", "不包含")

实际示例 #

text

检查关键词：
    A                        B
1   文本                     是否包含"excel"
2   Learn EXCEL functions    =ISNUMBER(SEARCH("excel", A2))  → TRUE
3   Learn Python             =ISNUMBER(SEARCH("excel", A3))  → FALSE

REPLACE 函数 #

语法 #

excel

=REPLACE(old_text, start_num, num_chars, new_text)

参数：
- old_text: 原文本
- start_num: 开始替换的位置
- num_chars: 要替换的字符数
- new_text: 替换为的文本

基本用法 #

excel

=REPLACE("hello", 1, 2, "HI")      → "HIllo"    替换前2个字符
=REPLACE("hello", 2, 3, "abc")     → "habco"    从第2位替换3个字符
=REPLACE("hello", 6, 0, " world")  → "hello world"  插入文本
=REPLACE("hello", 1, 5, "")        → ""         删除文本

实际应用 #

excel

隐藏手机号中间4位：
=REPLACE(A1, 4, 4, "****")

隐藏身份证中间8位：
=REPLACE(A1, 7, 8, "********")

插入分隔符：
=REPLACE(A1, 4, 0, "-")  在第4位插入横线

实际示例 #

text

手机号隐藏：
    A              B
1   手机号         隐藏后
2   13812345678    =REPLACE(A2, 4, 4, "****")  → "138****5678"

身份证隐藏：
    A                   B
1   身份证号            隐藏后
2   110101199001011234  =REPLACE(A2, 7, 8, "********")  → "110101********1234"

动态替换 #

excel

从某个字符开始替换到结尾：
=REPLACE(A1, FIND("@", A1), LEN(A1), "@newdomain.com")

替换特定位置后的内容：
=REPLACE(A1, 5, LEN(A1)-4, "...")

SUBSTITUTE 函数 #

语法 #

excel

=SUBSTITUTE(text, old_text, new_text, [instance_num])

参数：
- text: 原文本
- old_text: 要替换的文本
- new_text: 替换为的文本
- instance_num: 替换第几个（可选，默认全部）

基本用法 #

excel

=SUBSTITUTE("hello world", "world", "Excel")  → "hello Excel"
=SUBSTITUTE("aaa", "a", "b")                  → "bbb"  全部替换
=SUBSTITUTE("aaa", "a", "b", 1)               → "baa"  只替换第1个
=SUBSTITUTE("aaa", "a", "b", 2)               → "aba"  只替换第2个
=SUBSTITUTE("hello", "x", "y")                → "hello"  找不到则不变

实际应用 #

excel

删除特定字符：
=SUBSTITUTE(A1, "-", "")        删除所有横线
=SUBSTITUTE(A1, " ", "")        删除所有空格
=SUBSTITUTE(A1, CHAR(10), "")   删除换行符

替换分隔符：
=SUBSTITUTE(A1, ",", "-")       逗号改为横线

计算某字符出现次数：
=LEN(A1)-LEN(SUBSTITUTE(A1, "a", ""))

实际示例 #

text

删除空格：
    A              B
1   原文本         删除空格后
2   "hello world"  =SUBSTITUTE(A2, " ", "")  → "helloworld"

计算字符出现次数：
    A              B
1   文本           "a"出现次数
2   "banana"       =LEN(A2)-LEN(SUBSTITUTE(A2, "a", ""))  → 3

REPLACE vs SUBSTITUTE #

excel

REPLACE：按位置替换
=REPLACE("hello world", 7, 5, "Excel")  → "hello Excel"

SUBSTITUTE：按内容替换
=SUBSTITUTE("hello world", "world", "Excel")  → "hello Excel"

选择原则：
- 知道位置用 REPLACE
- 知道内容用 SUBSTITUTE

函数组合应用 #

提取两个分隔符之间的内容 #

excel

文本：A1 = "姓名：张三；年龄：25"

提取姓名：
=MID(A1, FIND("：", A1)+1, FIND("；", A1)-FIND("：", A1)-1)
→ "张三"

提取年龄：
=MID(A1, FIND("：", A1, FIND("；", A1)+1)+1, LEN(A1)-FIND("：", A1, FIND("；", A1)+1))
→ "25"

分割姓名 #

excel

姓名：张三

姓：=LEFT(A1, 1)              → "张"
名：=RIGHT(A1, LEN(A1)-1)     → "三"

姓名：欧阳锋（复姓）

姓：=LEFT(A1, 2)              → "欧阳"
名：=RIGHT(A1, LEN(A1)-2)     → "锋"

提取文件名和扩展名 #

excel

文件：report.xlsx

文件名：=LEFT(A1, FIND(".", A1)-1)           → "report"
扩展名：=RIGHT(A1, LEN(A1)-FIND(".", A1))    → "xlsx"

带路径的文件：C:\Users\Documents\report.xlsx

文件名：
=MID(A1, FIND("*", SUBSTITUTE(A1, "\", "*", LEN(A1)-LEN(SUBSTITUTE(A1, "\", ""))))+1, LEN(A1))
→ "report.xlsx"

格式化电话号码 #

excel

原始：13812345678

方法1（使用 REPLACE）：
=REPLACE(REPLACE(A1, 4, 0, "-"), 9, 0, "-")
→ "138-1234-5678"

方法2（使用 TEXT）：
=TEXT(A1, "000-0000-0000")
→ "138-1234-5678"

方法3（使用 LEFT/MID/RIGHT）：
=LEFT(A1, 3) & "-" & MID(A1, 4, 4) & "-" & RIGHT(A1, 4)
→ "138-1234-5678"

计算单词数 #

excel

=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1), " ", ""))+1

原理：
1. TRIM 清除多余空格
2. 计算总字符数
3. 删除所有空格后计算字符数
4. 差值就是空格数，单词数 = 空格数 + 1

提取第N个词 #

excel

提取第1个词：
=TRIM(LEFT(A1, FIND(" ", A1&" ")-1))

提取第2个词：
=TRIM(MID(A1, FIND(" ", A1)+1, FIND(" ", A1, FIND(" ", A1)+1)-FIND(" ", A1)-1))

提取第N个词（使用 FILTERXML，Excel 2013+）：
=FILTERXML("<t><s>" & SUBSTITUTE(A1, " ", "</s><s>") & "</s></t>", "//s[" & N & "]")

实战案例 #

案例1：解析CSV格式数据 #

text

数据：
    A                    B          C          D
1   原数据               字段1      字段2      字段3
2   "张三,男,25岁"       张三       男         25岁

字段1：=LEFT(A2, FIND(",", A2)-1)  → "张三"
字段2：=MID(A2, FIND(",", A2)+1, FIND(",", A2, FIND(",", A2)+1)-FIND(",", A2)-1)  → "男"
字段3：=RIGHT(A2, LEN(A2)-FIND(",", A2, FIND(",", A2)+1))  → "25岁"

案例2：处理URL参数 #

text

URL：https://example.com?name=zhangsan&age=25

提取name参数值：
=MID(A1, FIND("name=", A1)+5, FIND("&", A1&"&", FIND("name=", A1))-FIND("name=", A1)-5)
→ "zhangsan"

提取age参数值：
=MID(A1, FIND("age=", A1)+4, LEN(A1)-FIND("age=", A1)-3)
→ "25"

案例3：批量替换多个字符 #

text

需求：将多个字符替换为其他字符

原文本：A1 = "Hello-World_2024"

方法：嵌套 SUBSTITUTE
=SUBSTITUTE(SUBSTITUTE(A1, "-", " "), "_", " ")
→ "Hello World 2024"

替换多个字符：
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "-", ""), "_", ""), " ", "")
→ "HelloWorld2024"

案例4：提取括号内容 #

text

文本：产品名称（规格）

提取括号内容：
=MID(A1, FIND("（", A1)+1, FIND("）", A1)-FIND("（", A1)-1)

提取括号前的内容：
=LEFT(A1, FIND("（", A1)-1)

高级技巧 #

查找最后一个出现位置 #

excel

查找最后一个空格的位置：
=LOOKUP(1, 0/(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1)=" "), ROW(INDIRECT("1:"&LEN(A1))))

或（Excel 365）：
=MAX(IF(MID(A1, SEQUENCE(LEN(A1)), 1)=" ", SEQUENCE(LEN(A1))))

提取最后一个词 #

excel

=RIGHT(A1, LEN(A1)-LOOKUP(1, 0/(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1)=" "), ROW(INDIRECT("1:"&LEN(A1)))))

批量查找多个关键词 #

excel

检查是否包含任意一个关键词：
=OR(ISNUMBER(SEARCH({"Excel", "Word", "PPT"}, A1)))

检查包含几个关键词：
=SUMPRODUCT(--ISNUMBER(SEARCH({"Excel", "Word", "PPT"}, A1)))

小结 #

文本查找与替换函数是处理复杂文本数据的利器：

函数	用途	示例
FIND	查找位置（区分大小写）	`=FIND("l", "hello")`
SEARCH	查找位置（不区分大小写）	`=SEARCH("L", "hello")`
REPLACE	按位置替换	`=REPLACE("hello", 1, 2, "HI")`
SUBSTITUTE	按内容替换	`=SUBSTITUTE("hello", "l", "L")`

接下来，让我们学习日期与时间函数，掌握日期时间处理技巧。