Excel 文本转换函数 #
函数概览 #
文本转换函数用于改变文本的大小写、清除多余空格和不可见字符，是数据清洗的重要工具。
text
┌─────────────────────────────────────────────────────────────┐
│ 文本转换函数一览 │
├─────────────────────────────────────────────────────────────┤
│ │
│ UPPER 转大写 =UPPER("hello") → "HELLO" │
│ LOWER 转小写 =LOWER("HELLO") → "hello" │
│ PROPER 首字母大写 =PROPER("hello world") → "Hello World"│
│ TRIM 清除空格 =TRIM(" hello ") → "hello" │
│ CLEAN 清除不可见 =CLEAN(A1) │
│ VALUE 文本转数字 =VALUE("123") → 123 │
│ TEXT 数字转文本 =TEXT(123, "00000") → "00123" │
│ │
└─────────────────────────────────────────────────────────────┘
UPPER 函数 #
语法 #
excel
=UPPER(text)
将文本转换为大写
基本用法 #
excel
=UPPER("hello") → "HELLO"
=UPPER("Hello") → "HELLO"
=UPPER("HELLO") → "HELLO"
=UPPER("Hello World") → "HELLO WORLD"
=UPPER("中文") → "中文"（中文不受影响）
实际应用 #
excel
统一产品编码格式：
=UPPER(A1)
比较时忽略大小写：
=UPPER(A1)=UPPER(B1)
验证码转换：
=UPPER(输入的验证码)
实际示例 #
text
产品编码标准化：
A B
1 原编码 标准编码
2 abc123 =UPPER(A2) → "ABC123"
3 XYZ789 =UPPER(A3) → "XYZ789"
4 dEf456 =UPPER(A4) → "DEF456"
LOWER 函数 #
语法 #
excel
=LOWER(text)
将文本转换为小写
基本用法 #
excel
=LOWER("HELLO") → "hello"
=LOWER("Hello") → "hello"
=LOWER("hello") → "hello"
=LOWER("Hello World") → "hello world"
实际应用 #
excel
邮箱地址标准化：
=LOWER(A1)
用户名标准化：
=LOWER(A1)
URL标准化：
=LOWER(A1)
实际示例 #
text
邮箱地址标准化：
A B
1 原邮箱 标准邮箱
2 ZhangSan@Example.COM =LOWER(A2) → "zhangsan@example.com"
3 LI_SI@GMAIL.COM =LOWER(A3) → "li_si@gmail.com"
PROPER 函数 #
语法 #
excel
=PROPER(text)
将每个单词的首字母转换为大写
基本用法 #
excel
=PROPER("hello") → "Hello"
=PROPER("HELLO") → "Hello"
=PROPER("hello world") → "Hello World"
=PROPER("HELLO WORLD") → "Hello World"
=PROPER("john smith") → "John Smith"
实际应用 #
excel
姓名格式化：
=PROPER(A1)
地址格式化：
=PROPER(A1)
标题格式化：
=PROPER(A1)
注意事项 #
excel
PROPER 可能不适用于某些情况：
=PROPER("iPhone") → "Iphone"（错误）
=PROPER("McDonald") → "Mcdonald"（错误）
=PROPER("O'Brien") → "O'Brien"（正确）
对于特殊名称，需要手动调整
实际示例 #
text
姓名格式化：
A B
1 原姓名 格式化
2 zhang san =PROPER(A2) → "Zhang San"
3 LI SI =PROPER(A3) → "Li Si"
4 wang wu =PROPER(A4) → "Wang Wu"
TRIM 函数 #
语法 #
excel
=TRIM(text)
清除文本中的多余空格：
- 清除开头和结尾的空格
- 将多个连续空格变为单个空格
基本用法 #
excel
=TRIM(" hello ") → "hello"
=TRIM("hello world") → "hello world"
=TRIM(" hello world ") → "hello world"
=TRIM("hello") → "hello"
实际应用 #
excel
清除输入数据中的多余空格：
=TRIM(A1)
比较时忽略空格差异：
=TRIM(A1)=TRIM(B1)
配合其他函数使用：
=TRIM(UPPER(A1))
实际示例 #
text
数据清洗：
A B
1 原数据 清洗后
2 " 张三 " =TRIM(A2) → "张三"
3 "李 四" =TRIM(A3) → "李 四"
4 "王五 " =TRIM(A4) → "王五"
TRIM 不能清除的空格 #
excel
TRIM 只能清除普通空格（ASCII 32）
不能清除：
- 不间断空格（ASCII 160）
- 其他特殊空格字符
解决方法：
=TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160), " ")))
CLEAN 函数 #
语法 #
excel
=CLEAN(text)
删除文本中的不可打印字符（ASCII 0-31）
基本用法 #
excel
=CLEAN(A1) 清除不可打印字符
=CLEAN("hello"&CHAR(10)&"world") → "helloworld"
实际应用 #
excel
从网页或系统导入的数据清洗：
=CLEAN(A1)
配合 TRIM 使用：
=TRIM(CLEAN(A1))
完整清洗：
=TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160), " ")))
实际示例 #
text
从系统导入的数据：
A B
1 原数据 清洗后
2 "张三" =CLEAN(TRIM(A2)) → "张三"
（清除了不可见字符）
VALUE 函数 #
语法 #
excel
=VALUE(text)
将文本格式的数字转换为数值
基本用法 #
excel
=VALUE("123") → 123
=VALUE("123.45") → 123.45
=VALUE("-123") → -123
=VALUE("1,234") → 1234
=VALUE("2024-01-01") → 45292（日期序列号）
=VALUE("abc") → #VALUE!（错误）
实际应用 #
excel
文本数字转数值：
=VALUE(A1)
提取的数字转换：
=VALUE(MID(A1, 2, 3))
配合 IFERROR 使用：
=IFERROR(VALUE(A1), 0)
实际示例 #
text
数据转换：
A B
1 文本数字 数值
2 "123" =VALUE(A2) → 123
3 "456.78" =VALUE(A3) → 456.78
4 "1,000" =VALUE(A4) → 1000
TEXT 函数 #
语法 #
excel
=TEXT(value, format_text)
参数：
- value: 要格式化的数值
- format_text: 格式代码
常用格式代码 #
excel
数字格式：
=TEXT(123, "00000") → "00123" 补零
=TEXT(1234.5, "#,##0.00") → "1,234.50" 千分位
=TEXT(0.5, "0%") → "50%" 百分比
日期格式：
=TEXT(TODAY(), "yyyy-mm-dd") → "2024-01-15"
=TEXT(TODAY(), "yyyy年mm月dd日") → "2024年01月15日"
=TEXT(TODAY(), "dddd") → "Monday"
=TEXT(TODAY(), "aaa") → "一"（星期几中文）
时间格式：
=TEXT(NOW(), "hh:mm:ss") → "14:30:25"
=TEXT(NOW(), "上午/下午 hh:mm") → "下午 02:30"
实际应用 #
excel
格式化金额：
=TEXT(A1, "￥#,##0.00")
格式化日期：
=TEXT(A1, "yyyy年mm月dd日")
格式化电话号码：
=TEXT(A1, "000-0000-0000")
拼接带格式的文本：
="销售额：" & TEXT(A1, "￥#,##0.00")
实际示例 #
text
金额格式化：
A B
1 数值 格式化
2 1234.5 =TEXT(A2, "￥#,##0.00") → "￥1,234.50"
3 10000 =TEXT(A3, "￥#,##0.00") → "￥10,000.00"
日期格式化：
A B
1 日期 格式化
2 2024-01-15 =TEXT(A2, "yyyy年mm月dd日") → "2024年01月15日"
3 2024-01-15 =TEXT(A3, "aaaa") → "星期一"
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个）
实际应用 #
excel
删除特定字符：
=SUBSTITUTE(A1, "-", "") 删除所有横线
=SUBSTITUTE(A1, " ", "") 删除所有空格
替换分隔符：
=SUBSTITUTE(A1, ",", "-") 逗号改为横线
计算某字符出现次数：
=LEN(A1)-LEN(SUBSTITUTE(A1, "a", ""))
实际示例 #
text
电话号码格式化：
A B
1 原号码 格式化
2 13812345678 =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,
MID(A2,4,4), "****"),
MID(A2,1,3), LEFT(A2,3)),
MID(A2,8,4), RIGHT(A2,4))
→ "138****5678"
简化版隐藏：
=LEFT(A2,3) & "****" & RIGHT(A2,4) → "138****5678"
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"
=REPLACE("hello", 2, 3, "abc") → "habco"
=REPLACE("hello", 6, 0, " world") → "hello world"（插入）
SUBSTITUTE vs REPLACE #
excel
SUBSTITUTE：按内容替换
=SUBSTITUTE("hello world", "world", "Excel") → "hello Excel"
REPLACE：按位置替换
=REPLACE("hello world", 7, 5, "Excel") → "hello Excel"
实际应用 #
excel
隐藏身份证中间部分：
=REPLACE(A1, 7, 8, "********")
隐藏手机号中间部分：
=REPLACE(A1, 4, 4, "****")
插入分隔符：
=REPLACE(A1, 4, 0, "-") 在第4位插入横线
函数组合应用 #
完整数据清洗 #
excel
=TRIM(CLEAN(UPPER(A1)))
清除不可见字符、多余空格、转大写
=TRIM(CLEAN(PROPER(A1)))
清除不可见字符、多余空格、首字母大写
标准化邮箱 #
excel
=LOWER(TRIM(A1))
清除空格并转小写
格式化电话号码 #
excel
原始：13812345678
方法1：
=TEXT(A1, "000-0000-0000") → "138-1234-5678"
方法2：
=REPLACE(REPLACE(A1,4,0,"-"),9,0,"-") → "138-1234-5678"
提取并格式化 #
excel
从"订单号：ORD-2024-001"中提取订单号：
=TRIM(RIGHT(A1, LEN(A1)-FIND("：", A1)))
→ "ORD-2024-001"
实战案例 #
案例1：姓名数据清洗 #
text
数据：
A B
1 原姓名 清洗后
2 " 张三 " =PROPER(TRIM(A2)) → "张三"
3 "LI SI" =PROPER(TRIM(A3)) → "Li Si"
4 " wang wu " =PROPER(TRIM(A4)) → "Wang Wu"
案例2：产品编码标准化 #
text
数据：
A B
1 原编码 标准编码
2 " abc-123 " =UPPER(TRIM(A3)) → "ABC-123"
3 "XYZ-456" =UPPER(TRIM(A4)) → "XYZ-456"
4 " dEf-789 " =UPPER(TRIM(A5)) → "DEF-789"
案例3：金额格式化显示 #
text
数据：
A B
1 金额 显示
2 1234.5 ="金额：" & TEXT(A2, "￥#,##0.00") → "金额：￥1,234.50"
3 10000 ="金额：" & TEXT(A3, "￥#,##0.00") → "金额：￥10,000.00"
小结 #
文本转换函数是数据清洗和格式化的重要工具：
| 函数 | 用途 | 示例 |
|---|---|---|
| UPPER | 转大写 | =UPPER("hello") |
| LOWER | 转小写 | =LOWER("HELLO") |
| PROPER | 首字母大写 | =PROPER("hello world") |
| TRIM | 清除空格 | =TRIM(" hello ") |
| CLEAN | 清除不可见字符 | =CLEAN(A1) |
| VALUE | 文本转数字 | =VALUE("123") |
| TEXT | 数字转文本 | =TEXT(123, "00000") |
| SUBSTITUTE | 替换文本 | =SUBSTITUTE(A1, "a", "b") |
| REPLACE | 按位置替换 | =REPLACE(A1, 1, 2, "HI") |
接下来，让我们学习 文本查找与替换，掌握高级文本处理技巧。
最后更新：2026-04-11