📌 阶段四 · 进阶函数 · 第16讲 简单文本函数 · 约 45 分钟

让每张单据都有唯一身份证号：公式自动生成 RK-20240101-001 式单号，还能反向拆解编号

学习目标

  • 会用 LEFT/RIGHT/MID 从编号里截取需要的部分
  • 会用 LEN/LENB 区分字符与字节，隔空数出汉字个数
  • 会用 FIND 定位分隔符，配合截取函数精准拆分文本
  • 会用 & 和 TEXT 拼接生成规范单号 RK-20240101-001
  • 会反向拆解单号与商品编号，提取日期、序号和类别码

知识点讲解

LEFT/RIGHT：从两头截取

=LEFT(文本,[个数]) 从左边第一个字符开始返回指定个数的字符；第 2 参数不写默认取 1 个；超过文本总长时返回整个文本。

=RIGHT(文本,[个数]) 用法完全相同，只是从右边最后一个字符开始取（取出来的字符依然按从左到右的顺序排列）。

进销存里它们是「编号解码器」：单号 RK-20240101-001，=LEFT(A2,2) 取单据类型「RK」，=RIGHT(A2,3) 取当日序号「001」；商品编号 WJ001 用 =LEFT(A2,2) 取类别码「WJ」。

=LEFT(A2,3)

讲义示例：截取 A3 前 3 个字符——LEFT 负责左边的若干位。

=RIGHT(A2,3)

截取单号最后 3 位，得到当日序号 001。

💡 提示:

  • 第 2 参数省略时默认取 1 个字符
  • 截取出来的「数字」是文本，要参与计算需乘 1 转换（第 11 讲的格式坑）

MID：从中间任意位置下刀

=MID(文本,开始位置,个数)：参数 2 是从第几位开始，参数 3 是取多长。三个注意：参数 2 小于 1 返回错误值；参数 2 大于文本长度返回空；参数 3 大于剩余长度时，从开始位置一路取到结尾。

LEFT 和 RIGHT 嵌套可以替代 MID：先 =LEFT(E3,5) 截前 5 位，再 =RIGHT(LEFT(E3,5),3) 从中取后 3 位——理解了这条，MID 的「先定位后取长」就更清楚了。

进销存常用：=MID(A2,4,8) 从单号第 4 位起取 8 位，正好是日期 20240101。

=MID(A2,4,8)

从单号第 4 位开始取 8 个字符：R K - 后面正好是 8 位日期 20240101。

=RIGHT(LEFT(E3,5),3)

LEFT+RIGHT 嵌套替代 MID：先截前 5 位，再从结果里取后 3 位。

💡 提示:

  • 位置从 1 数起，不是从 0
  • 参数 3 拿不准就写大一点，让它自动取到文本结尾

LEN/LENB：数字符还是数字节

=LEN(文本) 返回字符个数，空格也计数；=LENB(文本) 返回字节数。区别在于：半角字母、数字、英文标点占 1 字节；汉字、全角字符、中文标点占 2 字节。

于是 LENB−LEN 就是汉字个数。经典用法：从「7852千克」里取单位 =RIGHT(A2,LENB(A2)-LEN(A2))——字符 6 个、字节 8 个，差 2 说明右边有 2 个汉字，直接从右侧取 2 个。

进销存里「100包」「24个」这类数量加单位混写的录入数据，就能这样干净拆分。

=LEN(A2)

A2 有几个字符，空格也算。

=RIGHT(A2,LENB(A2)-LEN(A2))

字节数减字符数=汉字个数，再从右侧截取——单位无论是一两个字都能取干净。

💡 提示:

  • LEFT/RIGHT/MID/LEN 都有对应的 B 版函数（LEFTB、RIGHTB、MIDB、LENB），区别就是字符与字节

FIND/SEARCH：先定位，再下刀

=FIND(要找的文本,在哪找,[开始位置]) 返回某个字符在文本中的位置：参数 1 可以是字符或数字，字符要加英文引号；参数 3 默认 1，从第一个字符开始找。找不到会返回错误值。

它有个兄弟 SEARCH：区别是 SEARCH 不区分大小写、支持通配符，FIND 区分大小写且不支持。

组合拳是文本处理的精髓：从邮箱地址取用户名 =LEFT(F2,FIND("@",F2,1)-1)——@ 在第几位，用户名就有几位；取域名 =MID(F2,FIND("@",F2)+1,100)。进销存同款：=LEFT(A2,FIND("-",A2)-1) 从单号里截出前缀 RK。

=LEFT(F2,FIND("@",F2,1)-1)

先定位 @ 的位置，减 1 就是用户名长度，交给 LEFT 截取——定长与变长问题一次解决。

=MID(F2,FIND("@",F2)+1,100)

@ 后一位开始取，长度写个够大的 100，自动取到结尾。

=LEFT(A2,FIND("-",A2)-1)

从单号里取第一个 - 之前的部分，得到单据类型 RK 或 CK。

💡 提示:

  • FIND 找不到时报错，重要公式外面包一层 IFERROR 兜底
  • 要忽略大小写或用通配符查找时换 SEARCH

& 连接与 TEXT：把零件拼成规范单号

文本函数负责拆，& 负责装（CONCATENATE 函数与 & 等价，但 & 更顺手）。

单号规则「RK-日期-当日序号」分三段拼接：日期段用第 14 讲见过的 TEXT，格式代码 yyyymmdd 把日期变成 20240101；序号段用 TEXT(x,"000") 把 1 变 001、12 变 012——补齐三位。

当日序号不用手工数：用第 9 讲的 COUNTIFS 做逐行扩大的计数器 =COUNTIFS($B$2:B2,B2)，数出「同一日期到本行为止是第几笔」，日期一换自动归 1。

=TEXT(B2,"yyyymmdd")

日期变 20240101 样式——TEXT 是拼接编号时补零、定型的关键工具。

=TEXT(3,"000")

数字补齐三位：3 变 003。

="RK-"&TEXT(B2,"yyyymmdd")&"-"&TEXT(COUNTIFS($B$2:B2,B2),"000")

完整单号公式：前缀 + 格式化日期 + 动态序号；$B$2:B2 锁头不锁尾，形成逐行扩大的同日计数器。

💡 提示:

  • 单号是文本，所在列别设成数值格式，否则前导零会被吃掉
  • COUNTIFS 计数区域「锁头不锁尾」是逐行累计的经典手法，第 9 讲学过单条件版

举一反三：用同一套函数解析身份证

身份证号 18 位（老证 15 位）结构：第 16 位地区码，第 714 位出生日期，第 15~17 位顺序码（18 位证的第 17 位、15 位证的第 15 位，奇数为男、偶数为女），第 18 位校验码。

拆解全靠这一讲的函数：地区码 =LEFT(B2,6)；出生日期 =DATE(MID(B2,7,4),MID(B2,11,2),MID(B2,13,2))；性别位 =IF(LEN(B13)=15,RIGHT(B13,1),MID(B13,17,1))。

一个熟悉的坑：身份证是文本格式，截出的 6 位地区码还是文本，而对照表里常是数字——匹配前要 *1 转换，又是第 11 讲那一招。

=LEFT(B2,6)

截取前 6 位地区码；与地区对照表匹配时记得 *1 转成数值。

=DATE(MID(B2,7,4),MID(B2,11,2),MID(B2,13,2))

MID 分别取出年、月、日，DATE 组装成真日期——拆与装的接力。

=IF(LEN(B13)=15,RIGHT(B13,1),MID(B13,17,1))

先判断是 15 位老证还是 18 位新证，再从对应位置取性别位。

💡 提示:

  • 客户档案要存身份证号，列格式务必先设文本——18 位数字会被 Excel 记成科学计数法且尾部归 0

实战演练

之前入库单、出库单的单号都是手工敲的，重号漏号全凭细心。这一讲把单号变成公式自动生成，规则「RK-日期-当日序号」，如 RK-20240102-001，出库单用 CK- 前缀；再反向练手，从单号里拆出日期、序号、类型，从商品编号里拆出类别码——让编号本身变成可分析的数据。这是期末系统「单据规范」的最后一块拼图。可先在右侧练习数据里试手感，再回到进销存工作簿正式建造。

  1. 入库单：单号自动化 — 清空入库单 A 列旧单号（或插入新单号列），A2 输入 ="RK-"&TEXT(B2,"yyyymmdd")&"-"&TEXT(COUNTIFS($B$2:B2,B2),"000")，下拉到全部流水行。A 列设为文本格式更稳妥。
  2. 检查跨日重置与同日递增 — 找两行日期不同的相邻记录，确认序号从 001 重新开始；再看同一天内的单号是否依次 001、002、003。COUNTIFS 的 $B$2:B2 区域在自动干这件事。
  3. 出库单换成 CK- 前缀 — 出库单 A2 输入 ="CK-"&TEXT(B2,"yyyymmdd")&"-"&TEXT(COUNTIFS($B$2:B2,B2),"000")，下拉。两张单据前缀不同、规则相同，从单号一眼可辨单据类型。
  4. 单号体检：长度与重复 — 空白格 =LEN(A2) 应等于 15（RK-20240101-001 共 15 个字符）；再 =COUNTIF(A:A,A2) 下拉应全为 1，说明无重号。发现异常先检查日期列格式是否真日期。
  5. 反向拆解单号 — 右侧空列练三条：=MID(A2,4,8) 取出日期串、=RIGHT(A2,3) 取当日序号、=LEFT(A2,FIND("-",A2)-1) 取单据类型。把日期串与 B 列对照，验证拆解正确。
  6. 商品编号拆类别码 — 商品档案 I 列（临时统计列）输入 =LEFT(A2,2)，下拉得到 WJ/ZP/SB；再升级 =IF(LEFT(A2,2)="WJ","办公文具",IF(LEFT(A2,2)="ZP","纸品","办公设备"))——不用查档案，从编号直接读出类别，与 C 列对照验证。
  7. 防呆双保险 — 给两单的 A 列设数据有效性：自定义公式 =COUNTIF(A:A,A2)<2（第 9 讲技巧回锅）。从此单号既由公式保证唯一，又被有效性兜底拦一道。

在线练习

下面的表格可以直接编辑,照着演练步骤在本页练习;需要菜单操作的步骤请下载配套练习文件,在自己电脑的 Excel 中完成。

表格加载中…

验收清单

逐项自查,全部通过即完成本讲:

  • ☐ 入库单、出库单的单号全部由公式生成，无手工输入残留
  • ☐ 同一天的单号序号连续，跨日自动归 001
  • ☐ LEN 体检全部为 15，COUNTIF 查重全部为 1
  • ☐ MID/RIGHT/LEFT 能从任一单号正确拆出日期、序号、类型
  • ☐ 商品档案类别码列与 C 列类别一一对应
  • ☐ A 列数据有效性已设置并测试过重复拦截