告别小白,每天5分钟!Excel基础练习30天打卡计划(第8天)

哈喽，小伙伴们，打卡第八天啦！👋

上一期我们学习了查找替换与定位条件，批量处理数据的能力大涨。但工作中还有一种常见情况：数据挤在一个单元格里，想拆分成多列——比如“张三-销售部-10000”，你想把它变成三列；或者从身份证号里提取出生日期和性别。

今天我们就来解决这个问题，用两招神技：数据分列 和 文本函数。学会了它们，再乱的文本也能被收拾得服服帖帖。

📅 练习背景设定

今天我们不直接使用之前的销售表，而是创建一个新的场景来练习文本处理。在 “销售练习-我的名字.xlsx” 中新建一个工作表，命名为 “文本处理练习”。我们将模拟从系统导出的杂乱数据，一步步把它清洗干净。

✍️ 今日练习题目（第8天）

核心技能：数据分列（分隔符号/固定宽度）、文本函数（LEFT、RIGHT、MID、LEN、FIND）、身份证号提取出生日期与性别、合并文本（&或CONCAT）。

第一部分：数据分列——一键拆分“张三-销售部-10000”

场景1：用分隔符号分列

假设我们从考勤系统导出了一列员工信息，格式是“姓名-部门-工资”，现在需要拆成三列。

1. 准备数据：

• 在A1输入 “员工信息” 作为表头。

• 在A2:A6依次输入：

  

2. 启动分列：

• 选中 A2:A6 数据区域。

• 点击菜单栏 “数据” 选项卡 → 在“数据工具”组中点击 “分列” 按钮。

3. 第1步：选择文件类型：

• 因为我们数据是用“-”分隔的，选择 “分隔符号” → 下一步。

4. 第2步：设置分隔符：

• 勾选 “其他”，在后面的输入框中输入 “-”（减号）。

• 下方预览区会显示拆分后的效果。

• 下一步。

5. 第3步：设置目标区域：

   结果：B列显示“张三”，C列显示“销售部”，D列显示“8000”。数据完美拆分！

• 默认目标区域是A2，但这样会覆盖原始数据。我们想保留原始列，把拆分结果放在B列开始。

• 在“目标区域”输入框里选择 B2（或输入 =$B$2）。

• 点击“完成”。

  

场景2：用固定宽度分列

如果数据没有明确分隔符，但每个字段长度固定（比如日期20240101），可以用固定宽度分列。

1. 准备数据：

• 在G1输入“日期代码”，G2:G4输入：

  

2. 启动分列，第1步选择 “固定宽度” → 下一步。

3. 建立分列线：在预览窗口中，鼠标点击刻度尺上的位置，添加分列线。比如在“年”和“月”之间（第4个字符后）点一下，再在“月”和“日”之间（第6个字符后）点一下。双击分列线可以删除。

4. 下一步，设置每一列的数据格式（比如第一列是文本，第二列是文本，第三列是数值），目标区域选 H2，完成。结果：H列“2024”，I列“01”，J列“05”，日期被拆成年、月、日。

第二部分：文本函数——更灵活的文本提取

分列适合规则明确的批量操作，但有时我们需要在公式中动态提取，比如从身份证号里自动提取出生日期、性别，这时文本函数就派上用场了。

场景3：从身份证号提取出生日期

假设有一列身份证号（18位），我们想自动计算出出生日期。

1. 准备数据：

• 在A8输入 “身份证号”，A9:A13输入几个虚拟身份证号（注意：18位，第7-14位是出生年月日）：

  

2. 用MID函数提取生日数字：

• MID函数语法：MID(文本, 起始位置, 字符个数)

• 在B9输入公式：=MID(A9,7,8)

• 解释：从A9的第7个字符开始，取8个字符（即19900307）。

• 向下填充，得到纯数字生日。

  

3. 将数字转换为日期格式：

• 可以使用DATE函数：=DATE(LEFT(B9,4), MID(B9,5,2), RIGHT(B9,2))

  

• 但更简单的方法是：选中B列，设置单元格格式为“日期”类型，但MID返回的是文本，直接设置格式无效。所以我们要用 =DATE(LEFT(A9,4), MID(A9,9,2), MID(A9,11,2)) 一步到位。

• 在C9输入：=DATE(MID(A9,7,4), MID(A9,11,2), MID(A9,13,2))

• 解释：第7-10位是年，第11-12位是月，第13-14位是日。

• 向下填充，C列显示标准的日期格式。

  

场景4：从身份证号提取性别

身份证第17位（倒数第二位）奇数为男性，偶数为女性。

1. 用MID取出第17位：

• 在D9输入：=MID(A9,17,1)，得到性别数字。

  

2. 用MOD和IF判断奇偶：

• 公式：=IF(MOD(MID(A9,17,1),2)=1,"男","女")

• 解释：MOD(数字,2)求余数，余数为1是奇数，为0是偶数。

  

• 向下填充，性别自动显示。

场景5：用FIND+LEN提取特定字符后的内容

有时数据没有固定分隔符，但有关键词，比如“产品:笔记本”，我们要提取冒号后的内容。

1. 准备数据：

   

2. 用FIND找到冒号位置，再用MID提取：

• 在F2输入：=MID(E2, FIND(":",E2)+1, LEN(E2))

• 解释：FIND(“:”,E2)找到冒号在第几个字符，+1就是冒号后的起始位置，LEN(E2)取足够长度。

• 向下填充，得到“笔记本”、“A5”、“礼盒装”。

  

场景6：用&或CONCAT合并文本

分列的反向操作是合并。比如把B列姓名、C列部门、D列工资合并成“张三-销售部-8000”。

1. 用&符号：

• 在I2输入：=B2&"-"&C2&"-"&D2，向下填充。

  

2. 用CONCAT或TEXTJOIN（2019以上版本）：

• =TEXTJOIN("-",TRUE,B2:D2)可以更灵活地合并区域。

  

第三部分：综合实战——清洗销售记录中的产品代码

让我们回到销售表的“1月销售记录”工作表，假设C列的产品名称后面还带着产品代码，比如“笔记本(NB-01)”，我们要把产品名称和代码分开。

1. 模拟数据：在C列中，将部分产品改成：

   

2. 提取产品名称（左括号前的部分）：

• 在辅助列输入：=LEFT(C4, FIND("(",C4)-1)

• 注意：如果某个单元格没有括号，FIND会报错，可以用IFERROR处理。

3. 提取产品代码（括号内的部分）：

• 公式：=MID(C4, FIND("(",C4)+1, FIND(")",C4)-FIND("(",C4)-1)

• 这个公式稍复杂，但掌握了MID+FIND组合后，任何夹在特定字符间的文字都能提取。

今日小贴士

• 分列与函数的取舍：如果是一次性清洗数据，分列更快捷；如果数据会动态增加，用函数公式能自动更新，更智能。

• 文本函数常用组合：

• LEFT + FIND：提取某个分隔符前的文本。

• MID + FIND：提取两个分隔符之间的文本。

• RIGHT + LEN + FIND：提取某个分隔符后的文本。

• LEN函数：返回文本长度，常与RIGHT配合使用，比如 =RIGHT(A1, LEN(A1)-FIND("@",A1)) 提取邮箱的域名。

• 身份证号处理注意：身份证号超过15位，在Excel中直接输入会变成科学计数法。务必先将单元格格式设为“文本”，或在输入前加一个单引号 '。

• TEXT函数：提取出生日期后，可以用 =TEXT(MID(A9,7,8),"0000-00-00") 直接转换为日期文本，再用 -- 或 *1 转换为真正日期。

恭喜你！今天你学会了数据分列和文本函数，从此不再畏惧杂乱文本。无论是清洗系统导出数据，还是从身份证号中提取信息，你都能轻松应对。Excel的文本处理能力，你已经掌握了核心的一半！

明天预告：数据验证（有效性）的进阶用法。如何制作下拉菜单？如何实现二级联动？如何限制输入范围并给出友好提示？明天的内容让你的表格更“智能”，别人录入数据时再也不会出错！

记得保存文件，我们明天见！有任何问题评论区留言~ 👋

今日练习文件下载：（需要加群的请加我v527240310）

链接：https://pan.quark.cn/s/858eafdb13af