Excel 文本函数与数据处理

🎯 核心概念

文本函数用于对字符串进行精细化操作，包括从文本中提取特定部分、计算长度、定位字符以及合并多个文本片段。

核心作用：数据清洗、格式化和标准化的核心工具。

1. 函数概览

函数	功能	典型用途
`LEFT`	从左侧提取字符	提取前缀、编码前几位
`RIGHT`	从右侧提取字符	提取后缀、文件扩展名
`MID`	从中间提取字符	提取指定位置的子串
`LEN`	计算文本长度	辅助动态计算、数据验证
`SEARCH`	查找字符位置（不区分大小写）	定位分隔符、动态提取
`FIND`	查找字符位置（区分大小写）	精确匹配查找
`CONCATENATE`	合并文本	拼接多个字段
`&`	合并文本（运算符）	更简洁的文本拼接

2. 位置提取函数

2.1 LEFT - 左侧提取

vba

=LEFT(text, [num_chars])

参数	说明
`text`	要提取的文本
`num_chars`	提取的字符数（可选，默认为 1）

示例：

vba

=LEFT("ACM-111-WW", 3)     ' 返回 "ACM"
=LEFT(A2, 5)               ' 返回 A2 单元格的前5个字符

2.2 RIGHT - 右侧提取

vba

=RIGHT(text, [num_chars])

示例：

vba

=RIGHT("ACM-111-WW", 2)    ' 返回 "WW"
=RIGHT(A2, 4)              ' 返回 A2 单元格的后4个字符

2.3 MID - 中间提取

vba

=MID(text, start_num, num_chars)

参数	说明
`text`	要提取的文本
`start_num`	起始位置（从 1 开始计数）
`num_chars`	提取的字符数

示例：

vba

=MID("ACM-111-WW", 5, 3)   ' 返回 "111"（从第5位开始取3个字符）
=MID(A2, 2, 4)             ' 返回 A2 单元格从第2位开始的4个字符

3. 辅助函数

3.1 LEN - 长度计算

vba

=LEN(text)

返回文本的字符总数（包括空格）。

示例：

vba

=LEN("Hello World")        ' 返回 11
=LEN(A2)                   ' 返回 A2 单元格的字符数

3.2 SEARCH - 字符定位（不区分大小写）

vba

=SEARCH(find_text, within_text, [start_num])

参数	说明
`find_text`	要查找的文本
`within_text`	被查找的文本
`start_num`	起始搜索位置（可选，默认为 1）

特性：不区分大小写

示例：

vba

=SEARCH(" ", "Patrick Marleau")    ' 返回 8（空格在第8位）
=SEARCH("a", "Patrick")            ' 返回 2

3.3 FIND - 字符定位（区分大小写）

vba

=FIND(find_text, within_text, [start_num])

与 SEARCH 功能相同，但区分大小写。

示例：

vba

=FIND("a", "Patrick")              ' 返回 2
=FIND("A", "Patrick")              ' 返回 #VALUE! 错误（未找到大写A）

3.4 SEARCH vs FIND 对比

特性	SEARCH	FIND
大小写敏感	否	是
通配符支持	支持（`*` 和 `?`）	不支持
推荐场景	常规查找	精确匹配

4. 文本合并

4.1 CONCATENATE 函数

vba

=CONCATENATE(text1, [text2], ...)

示例：

vba

=CONCATENATE("Hello", " ", "World")    ' 返回 "Hello World"
=CONCATENATE(A2, "-", B2)              ' 合并 A2 和 B2，中间加连字符

4.2 & 运算符

功能与 CONCATENATE 相同，语法更简洁，推荐使用。

vba

=text1 & text2 & text3

示例：

vba

="Hello" & " " & "World"               ' 返回 "Hello World"
=A2 & " " & B2                         ' 合并 A2 和 B2，中间加空格
=C4 & " " & B4                         ' "Joe" & " " & "Gonzales" → "Joe Gonzales"

4.3 合并方式对比

方式	语法	特点
`CONCATENATE`	`=CONCATENATE(A1, B1)`	传统函数，参数明确
`&` 运算符	`=A1 & B1`	更简洁，推荐使用
`TEXTJOIN`（新版）	`=TEXTJOIN(",", TRUE, A1:A10)`	支持分隔符和忽略空值

5. 应用场景

5.1 解析编码结构

场景：SKU 编码 ACM-111-WW 需分解为供应商 ID、部件号、产品码

vba

' 供应商ID（前3位）
=LEFT(A4, 3)                           ' → "ACM"

' 产品码（后2位）
=RIGHT(A4, 2)                          ' → "WW"

' 部件号（第5-7位）
=MID(A4, 5, 3)                         ' → "111"

5.2 分离姓名

场景：完整姓名 "Patrick Marleau" 分离为名和姓

vba

' 提取名（First Name）- 空格前的部分
=LEFT(B2, SEARCH(" ", B2) - 1)         ' → "Patrick"

' 提取姓（Last Name）- 空格后的部分
=RIGHT(B2, LEN(B2) - SEARCH(" ", B2))  ' → "Marleau"

公式解析：

步骤	公式	结果
1. 找空格位置	`SEARCH(" ", B2)`	8
2. 计算名的长度	`8 - 1`	7
3. 提取名	`LEFT(B2, 7)`	"Patrick"
4. 计算姓的长度	`LEN(B2) - 8`	7
5. 提取姓	`RIGHT(B2, 7)`	"Marleau"

5.3 处理变长数据

场景：SKU 编码长度不一（8 位或 10 位），产品码分别为后 2 位或后 4 位

vba

=IF(LEN(A19)=10, RIGHT(A19, 4), RIGHT(A19, 2))

5.4 合并姓名

场景：B 列为姓，C 列为名，合并为 "名姓" 格式

vba

=C4 & " " & B4                         ' → "Joe Gonzales"

5.5 生成标准化编码

场景：根据多个字段生成统一编码

vba

=A2 & "-" & B2 & "-" & TEXT(C2, "0000")
' 如 "PRD" & "-" & "A1" & "-" & "0023" → "PRD-A1-0023"

6. 常见问题与注意事项

6.1 静态 vs 动态提取

⚠️ 避免硬编码

硬编码字符数无法适应不同长度的文本：

vba

' 不推荐：硬编码数字
=LEFT(A2, 7)

' 推荐：使用 SEARCH 动态计算
=LEFT(A2, SEARCH("-", A2) - 1)

6.2 差一错误（Off-by-One）

💡 注意

使用 SEARCH 定位分隔符时，提取分隔符前的内容需要 减 1：

vba

' 错误：包含了空格
=LEFT(B2, SEARCH(" ", B2))             ' → "Patrick "

' 正确：排除空格
=LEFT(B2, SEARCH(" ", B2) - 1)         ' → "Patrick"

6.3 空格处理

合并文本时需手动添加空格，否则文本会粘连：

vba

' 错误：无空格
=A2 & B2                               ' → "JoeGonzales"

' 正确：添加空格
=A2 & " " & B2                         ' → "Joe Gonzales"

6.4 查找失败处理

💡 错误处理

SEARCH 或 FIND 找不到目标时返回 #VALUE! 错误，使用 IFERROR 处理：

vba

=IFERROR(SEARCH("-", A2), 0)           ' 找不到时返回 0
=IFERROR(LEFT(A2, SEARCH(" ", A2)-1), A2)  ' 无空格时返回原文本

7. 快速参考

提取函数模板

vba

=LEFT(文本, 字符数)                    ' 从左提取
=RIGHT(文本, 字符数)                   ' 从右提取
=MID(文本, 起始位置, 字符数)           ' 从中间提取

动态提取模板

vba

' 提取分隔符前的部分
=LEFT(文本, SEARCH(分隔符, 文本) - 1)

' 提取分隔符后的部分
=RIGHT(文本, LEN(文本) - SEARCH(分隔符, 文本))

' 提取两个分隔符之间的部分
=MID(文本, SEARCH(分隔符1, 文本) + 1, SEARCH(分隔符2, 文本) - SEARCH(分隔符1, 文本) - 1)

合并模板

vba

=文本1 & 分隔符 & 文本2                ' 推荐使用 &
=CONCATENATE(文本1, 分隔符, 文本2)     ' 传统方式

函数选择指南

需求	推荐函数
提取固定位置字符	`LEFT` / `RIGHT` / `MID`
按分隔符动态提取	`LEFT` / `RIGHT` + `SEARCH`
计算文本长度	`LEN`
查找字符位置	`SEARCH`（不区分大小写）
精确匹配查找	`FIND`（区分大小写）
合并文本	`&` 运算符

← 返回生产效率工具

Excel 文本函数与数据处理 ​

1. 函数概览 ​

2. 位置提取函数 ​

2.1 LEFT - 左侧提取 ​

2.2 RIGHT - 右侧提取 ​

2.3 MID - 中间提取 ​

3. 辅助函数 ​

3.1 LEN - 长度计算 ​

3.2 SEARCH - 字符定位（不区分大小写） ​

3.3 FIND - 字符定位（区分大小写） ​

3.4 SEARCH vs FIND 对比 ​

4. 文本合并 ​

4.1 CONCATENATE 函数 ​

4.2 & 运算符 ​

4.3 合并方式对比 ​

5. 应用场景 ​

5.1 解析编码结构 ​

5.2 分离姓名 ​

5.3 处理变长数据 ​

5.4 合并姓名 ​

5.5 生成标准化编码 ​

6. 常见问题与注意事项 ​

6.1 静态 vs 动态提取 ​

6.2 差一错误（Off-by-One） ​

6.3 空格处理 ​

6.4 查找失败处理 ​

7. 快速参考 ​

提取函数模板 ​

动态提取模板 ​

合并模板 ​

函数选择指南 ​