夜雨聆风开头：那些年，我们一起熬过的数据清洗夜
你有没有过这种经历？
凌晨两点，领导甩过来一份从系统导出的Excel表格，丢下一句"明天早上8点前整理好给我"就消失了。你打开一看，差点原地去世——
●省份和城市挤在同一个单元格里，前面是简称后面是全名，格式乱七八糟
●身份证号和手机号混在一列里，有的前面有空格有的没有
●邮箱地址格式五花八门，有的带前缀有的不带
●订单号里夹杂着各种特殊符号，想筛选都筛选不了
你叹了口气，开始一格一格地复制粘贴、手动删除。两小时过去了，眼睛花了，手酸了，才处理了不到三分之一。你开始怀疑人生：我到底是来做数据分析的，还是来当打字员的？
朋友，如果你也有过这种经历，那今天这篇文章就是为你量身定做的。
今天要给大家介绍的，是Excel里最常用的5个文本函数：LEFT、RIGHT、MID、FIND、SUBSTITUTE。学会它们，刚才那种两小时的手工活，5分钟就能搞定。
别不信，我刚工作那会，第一次用SUBSTITUTE批量去除空格的时候，差点哭出来——原来我之前浪费了那么多时间！
函数1：LEFT——从左边提取字符
语法说明
LEFT(文本, 提取长度)
●文本：你要处理的原始数据
●提取长度：从左边开始数，提取几个字符
是不是很简单？就像切面包，从左边切，你说切几厘米就切几厘米。
真实工作场景：提取省份简称
场景背景：
我第一份工作是在电商公司做运营，每天都要处理订单数据。系统导出的收货地址里，省份和城市是连在一起的，比如"浙江省杭州市"、"江苏省南京市"。领导让我统计每个省的订单量，可省份和城市挤在一起，根本没法分组统计。
手动做法： 一列一列地复制粘贴，把省份单独拎出来。3000条数据，我足足搞了一个多小时，眼睛都看花了，还时不时看错行。
函数做法：
=LEFT(A2, 3)
等等，为什么是3？因为"浙江省"是3个字，"江苏省"也是3个字。但是！这里有个坑——
常见坑点
坑1：省份字数不统一！
有的省份是3个字（四川省、浙江省），有的是4个字（黑龙江省），还有的带"自治区"就更长了。直接写LEFT(A2,3)遇到黑龙江就会少一个字。
那怎么办？ 别急，后面讲到FIND函数的时候，我会教你怎么动态定位"省"字的位置，想提取多长就提取多长。
坑2：中英文混排时，一个中文算几个字符？
答案是：LEFT函数里，一个中文和一个英文都算1个字符。所以"Abc测试"用LEFT提取前3个，得到的是"Abc"，不是"Ab"。
💡 **小技巧：** 如果你想按字节数提取（1个中文=2个字节），用LEFTB函数。但99%的情况下，用LEFT就够了。
函数2：RIGHT——从右边提取字符
语法说明
RIGHT(文本, 提取长度)
和LEFT是一对好兄弟，一个从左边切，一个从右边切。
真实工作场景：提取文件后缀名
场景背景：
有一次市场部的同事让我帮忙，她手里有几百个文件名，比如"活动方案V2最终版.docx"、"产品介绍PPT.pptx"、"数据报表.xlsx"，她想统计一下各种文件类型的数量。
手动做法： 一个个看，一个个数。几百个文件，看到最后眼都直了，".docx"和".doc"都分不清了。
函数做法：
=RIGHT(A2, 5)
（因为".docx"、".xlsx"、".pptx"都是5个字符）
这样就能把后缀名提取出来了。提取完之后，用数据透视表一拖，各种文件类型的数量立刻就出来了，前后不超过30秒。
另一个场景：提取手机号后四位
做用户运营的同学经常需要给用户发验证码或者做脱敏处理，只显示手机号后四位。这时候RIGHT函数就派上用场了：
=RIGHT(A2, 4)
一键搞定，比你手动选中后四位复制快多了。
常见坑点
坑1：提取长度超过文本长度怎么办？
比如你的文本只有3个字，但你写了`RIGHT(A2, 5)`，Excel不会报错，只会把整个文本都返回给你。听起来好像还挺智能的，但有时候会出问题——比如你本来想提取手机号后4位，但有的手机号少写了一位，变成了10位，RIGHT还是会正常返回最后4位，你根本发现不了数据有问题。
坑2：有看不见的空格怎么办？
这是新手最容易踩的坑！看起来单元格里就是"13800138000"，但如果后面有空格，你用RIGHT提取后4位，得到的就不对了。
💡 **解决办法：** 外面套一层TRIM函数，`=TRIM(RIGHT(A2,4))`，或者用后面要讲的SUBSTITUTE把空格全部干掉。
函数3：MID——从中间提取字符
语法说明
MID(文本, 起始位置, 提取长度)
LEFT是从左边切，RIGHT是从右边切，MID就是从中间切。你要告诉它两件事：从第几个字符开始切，切多长。
真实工作场景：提取身份证中的出生日期
场景背景：
刚做HR那会，有一次要给全公司员工发生日礼物，需要从身份证号里把出生日期提取出来。几百个员工的信息，我当时傻乎乎地一个个看身份证号，把第7到14位抄出来。抄到第50个的时候，我感觉眼睛都要瞎了。
后来一个老同事看不下去了，教了我MID函数，我当场就傻了——原来还能这样？！
函数做法：
身份证号一共18位，第7位到第14位是出生日期。比如身份证号"110101199001011234"，出生日期就是1990年1月1日。
=MID(A2, 7, 8)
●7：从第7位开始
●8：提取8个字符
就这么简单！一拉到底，几秒钟搞定。提取出来是"19900101"这种格式，如果想要变成"1990-01-01"，可以用TEXT函数格式化一下：
=TEXT(MID(A2,7,8), "0000-00-00")
瞬间就专业了有没有！
另一个场景：提取订单号中间段
有的公司订单号规则很复杂，比如"ORD-20240101-00123"，中间那段是日期，后面是序号。用MID也能轻松提取：
=MID(A2, 5, 8)// 提取日期部分：20240101
=MID(A2, 14, 5)// 提取序号部分：00123
常见坑点
坑1：起始位置是从1开始数的，不是从0开始！
这是程序员转行做Excel最容易犯的错。很多编程语言里字符串是从0开始索引的，但Excel里的MID函数，第一个字符的位置是1，不是0。别问我怎么知道的，说多了都是泪。
坑2：15位的老身份证号怎么办？
是的，还有一些老身份证号是15位的，出生日期是第7到12位（没有19的前缀）。这时候可以用LEN函数判断一下长度：
=IF(LEN(A2)=18, MID(A2,7,8), "19"&MID(A2,7,6))
不过现在15位的身份证已经很少见了，知道有这么回事就行。
坑3：提取长度写多了会怎样？
和RIGHT一样，不会报错，会把剩下的所有字符都返回。比如文本只有10个字符，你写`MID(A2, 8, 10)`，会返回第8、9、10位这3个字符，而不是报错。
函数4：FIND——查找字符位置
语法说明
FIND(要找的字符, 在哪里找, [从第几位开始找])
FIND函数的作用是：告诉你某个字符在文本里的位置是第几。它返回的是一个数字——位置编号。
这个函数本身不提取文本，但是它和LEFT/RIGHT/MID配合起来用，威力无穷。
真实工作场景：找到@符号的位置
场景背景：
做用户运营的时候，经常需要从邮箱地址里提取用户名（@前面的部分）或者域名（@后面的部分）。
比如"zhangsan@163.com"，用户名是"zhangsan"，域名是"163.com"。
手动做法： 选中@前面的文字，复制粘贴。几千个邮箱，你想想那画面。
用FIND定位：
=FIND("@", A2)
如果A2是"zhangsan@163.com"，这个公式会返回9，因为@在第9个字符的位置。
知道了@的位置，提取用户名就简单了：
=LEFT(A2, FIND("@", A2) - 1)
为什么要减1？因为@本身占了一个位置，用户名是@前面的部分，所以要减1。
提取域名呢？用RIGHT配合一下：
=RIGHT(A2, LEN(A2) - FIND("@", A2))
LEN是获取文本总长度，总长度减去@的位置，就是@后面有多少个字符。
这就是动态截取的思路——不管前面有多长，我都能精准地切到你想要的位置。
常见坑点
坑1：FIND是区分大小写的！
`FIND("A", "Apple")` 返回1，但 `FIND("a", "Apple")` 会返回#VALUE!错误，因为找不到小写的a。
如果你不需要区分大小写，用SEARCH函数，用法和FIND一模一样，但不区分大小写。
坑2：找不到会报错
如果文本里没有你要找的字符，FIND会返回#VALUE!错误。这个错误会沿着公式一直传下去，导致整个单元格报错。
解决办法： 用IFERROR函数包一层：
=IFERROR(FIND("@", A2), 0)
找不到的话就返回0，这样后面的公式就不会报错了。
坑3：第三个参数你可能没用过
FIND还有第三个可选参数——从第几位开始找。默认是从第1位开始找。
什么时候会用到呢？比如文本里有两个相同的字符，你想找第二个出现的位置：
=FIND("-", A2, FIND("-", A2) + 1)
这个公式的意思是：先找到第一个"-"的位置，然后从那个位置的下一位开始找，找到第二个"-"的位置。
函数5：SUBSTITUTE——替换文本
语法说明
SUBSTITUTE(文本, 旧内容, 新内容, [替换第几个])
这个函数可以说是数据清洗的"万金油"，哪里不干净换哪里。
真实工作场景：去除所有空格
场景背景：
这绝对是我用得最多的场景，没有之一。从各种系统导出来的数据，十有八九都有莫名其妙的空格——有的在开头，有的在结尾，有的在中间。
这些空格肉眼很难发现，但会导致VLOOKUP匹配失败、筛选漏掉数据、合并计算出错……各种诡异的问题。
我刚工作的时候，有一次做VLOOKUP，明明两边的数据看起来一模一样，但就是匹配不上。我折腾了快一个小时，最后才发现——其中一列的每个单元格后面都多了一个空格！
当时我的心情，就像发现了世界的真相。
函数做法：
=SUBSTITUTE(A2, " ", "")
就这么简单！把所有的空格替换成空，也就是全部删掉。
注意：这个函数会把所有位置的空格都删掉，包括中间的。如果你只想去掉首尾的空格，用TRIM函数就行。但我的经验是——数据清洗的时候，能删的空格都删掉，留着也是祸害。
其他实用场景
场景1：替换敏感词
=SUBSTITUTE(A2, "脏话", "***")
做内容审核的同学应该懂的。
场景2：统一格式
比如有的电话号用"-"分隔，有的用" "分隔，有的什么都没有，全部统一成没有分隔符的：
=SUBSTITUTE(SUBSTITUTE(A2, "-", ""), " ", "")
两层嵌套，先干掉"-"，再干掉空格。
场景3：批量修改文件名前缀
比如把"IMG_001.jpg"批量改成"photo_001.jpg"：
=SUBSTITUTE(A2, "IMG_", "photo_")
常见坑点
坑1：替换全部还是替换第几个？
SUBSTITUTE默认会替换所有匹配的内容。如果你只想替换第几个，可以用第四个参数：
=SUBSTITUTE(A2, "-", "", 2)
这个公式的意思是：只把第二个"-"替换掉，第一个不动。
什么时候用？比如"2024-01-01"，你想把第二个"-"去掉变成"2024-0101"，就可以用这个方法。
坑2：区分大小写
和FIND一样，SUBSTITUTE也是区分大小写的。`SUBSTITUTE("Apple", "a", "o")` 不会有任何变化，因为找不到小写的a。
坑3：特殊字符怎么替换？
换行符怎么办？有时候单元格里有换行（按Alt+Enter输入的），想去掉怎么办？
用CHAR(10)代表换行符：
=SUBSTITUTE(A2, CHAR(10), "")
同理，CHAR(9)是制表符Tab，CHAR(13)是回车符。
组合实战：1+1>2的威力
单个函数已经很厉害了，但真正的高手都是组合拳。接下来给大家演示两个经典的组合用法。
组合1：FIND + MID 提取两个符号中间的内容
举个例子："姓名<邮箱>"这种格式，比如"张三"，怎么把邮箱提取出来？
思路是：找到"<"的位置和">"的位置，然后用MID提取中间的部分。
=MID(A2, FIND("<", A2) + 1, FIND(">", A2) - FIND("<", A2) - 1)
拆解一下：
●`FIND("<", A2) + 1`：从"<"的下一位开始
●`FIND(">", A2) - FIND("<", A2) - 1`：">"的位置减去"<"的位置，再减1，就是中间邮箱的长度
这个公式看起来有点复杂，但思路很清晰：先定位起点和终点，再截取中间的内容。
掌握了这个思路，任何夹在两个符号中间的内容，你都能轻松提取出来。
组合2：SUBSTITUTE + LEN 统计字符出现次数
这个组合是我偶然间发现的，当时觉得太妙了！
问题：怎么统计一个文本里某个字符出现了多少次？
比如"A-001-B-002-C-003"里有多少个"-"？
思路是这样的：
1. 先算原始文本的长度
2. 把"-"全部替换掉，再算一次长度
3. 两个长度相减，就是"-"出现的次数
公式：
=LEN(A2) - LEN(SUBSTITUTE(A2, "-", ""))
是不是很巧妙？
我第一次用这个方法是在处理一组标签数据的时候——每个单元格里有多个标签，用逗号分隔，我需要统计每个单元格有几个标签。用这个方法，几秒钟就搞定了。
而且你还可以延伸一下：如果标签之间是用逗号分隔的，那么标签的数量 = 逗号的数量 + 1：
=LEN(A2) - LEN(SUBSTITUTE(A2, ",", "")) + 1
完美！
结尾：速记口诀 + 新手避坑指南
5个函数速记口诀
最后给大家编了个顺口溜，方便记忆：
**左边切，用LEFT，想切几位写几位；**
**右边切，用RIGHT，从右往左数明白；**
**中间切，用MID，起点长度都要记；**
**找位置，用FIND，定位准了再开切；**
**换内容，SUBSTITUTE，脏数据来全跑路。**
是不是还挺顺口的？多读两遍就记住了。
新手避坑指南
最后再给大家提几个新手最容易踩的坑，帮你们少走弯路：
1. 先备份再操作
不管你对自己的公式多有信心，操作之前先把原始数据复制一份备份。万一公式写错了、不小心覆盖了，哭都来不及。
2. 注意区分大小写
FIND和SUBSTITUTE都是区分大小写的，如果不需要区分，分别用SEARCH（代替FIND）；SUBSTITUTE没有不区分大小写的版本，这时候可以先把文本全部转成大写（UPPER）或小写（LOWER）再处理。
3. 数字和文本是两回事
用文本函数提取出来的数字，格式是文本型的。如果你后面要用来计算，记得转成数值格式。最简单的方法是乘以1：`=LEFT(A2,2)*1`。
4. 报错不可怕，可怕的是不报错但结果错了
很多新手看到#VALUE!就慌了，其实报错反而是好事——至少你知道哪里出问题了。更可怕的是公式不报错，但算出来的结果是错的，而你还不知道。
所以每次写完公式，一定要手动验证几个，确保结果正确再往下拉。
5. 文本函数只是入门，真正的大神用正则
当然，这5个函数只能处理一些常规的数据清洗需求。如果遇到更复杂的情况，比如提取一段文字中的所有数字、匹配某种特定格式的内容，那就需要用到正则表达式了。
不过对于90%的职场场景来说，今天讲的这5个函数已经足够让你从"手动两小时"变成"自动化5分钟"了。
写在最后：
我刚学Excel的时候，总觉得这些函数太简单了，不屑于学。结果每次遇到数据清洗的活，都要硬着头皮手动干，效率低得吓人。
后来我才明白：真正的效率提升，往往不是靠什么高大上的技巧，而是把最基础的工具用到极致。
LEFT、RIGHT、MID、FIND、SUBSTITUTE——这5个函数看起来简单，但它们组合起来的威力，远超你的想象。
今天就打开你的Excel，找一份待清洗的数据，试着用这些函数处理一下。相信我，当你看到几秒钟就搞定了以前几小时的工作量时，你会回来感谢我的。
我是沈未迟，专注分享职场效率工具。如果这篇文章对你有帮助，欢迎点个在看，转发给你身边还在手动洗数据的朋友~