夜雨聆风学习资料网

ARTICLE · 1361443

Excel小技巧:「分列」三步,把挤成一坨的一列拆成三列

Excel小技巧:「分列」三步,把挤成一坨的一列拆成三列

有没有遇到过系统导出的一张花名册,所有信息全挤在一 列:

张三-13800138000-上海-销售部李四-13900139000-北京-技术部

HR 要你拆成四列,姓名、手机号、城市、部门,方便做透视。

你大概会想起前期咱们学的 Ctrl+E 快速填充。填一个示例,按一下,确实能成。但这一列有 3 万人,其中 200 条的手机号是空的、30 条的部门字段里也带横杠(“销售-渠道部”),Ctrl+E 学出来的规律在这些行上直接错位,你还得逐行排查。

这种情况,Excel 有个更可控的功能:分列。

它有专门的选项管住数据类型、能丢弃不要的列,出问题也好定位。今天把这三步向导讲透,重点在第 3 步——绝大多数人只用前两步,坑全在第 3 步埋着。

位置与整体流程

选中那一列 → 数据 → 分列(WPS 相同)。

向导三步,每步的作用:

步骤
决定什么
第 1 步
数据类型:分隔符号 还是 固定宽度
第 2 步
具体分隔符是哪个、引号怎么识别
第 3 步
每一列拆出来是什么类型
、目标位置在哪、哪一列直接丢弃

第 1、2 步是"怎么切",第 3 步是"切出来的东西怎么存"。数据出事基本都出在第 3 步。

第 1 步:分隔符号 or 固定宽度

分隔符号:字段之间有明显的分隔字符。横杠、逗号、空格、Tab、竖线、中文顿号都算。这是最常见的情况。

固定宽度:字段之间没有分隔符,但每个字段占固定字符数。典型场景是从老系统、ERP 导出的定长文本,比如每行前 6 位是工号、中间 10 位是姓名、后 8 位是日期。

选固定宽度后,第 2 步界面会变成一把标尺,你在标尺上单击就画一条分列线,拖动可以调整位置,双击那条线就删掉。字段有长有短的时候,用这个模式比硬找分隔符靠谱。

一个判断方法: 如果某两个字段的分界位置每行都一样,用固定宽度;如果分界位置会变、但中间一定有某个符号,用分隔符号。两个都不满足(比如城市名有的两个字有的五个字,分隔符又是空格,地址里也有空格),那要交给公式或正则,别硬用分列。

第 2 步:分隔符怎么勾才准

选"分隔符号"后,下面有几个复选框:Tab、逗号、空格、分号、以及其他。

四个要点:

  1. 可以同时勾多个。
     比如数据里有的用逗号有的用分号,两个都勾上一起当分隔符处理。
  2. "其他"可以填任意字符。
     填横杠 -、填 】、填中文顿号 、。注意全角半角要和数据里一致,你填半角横杠去拆全角横杠的数据,一条都切不开。
  3. 勾上"连续分隔符号视为单个处理"。
     如果数据里有 张三--上海 这种连着两个横杠的,不勾这个会被切出一个空列,整行往后错位。
  4. "文本识别符号"默认是双引号。
     这一项很关键:CSV 里 "北京,朝阳" 这种被引号包住的字段,内部逗号不会被误当分隔符——前提是这个下拉保持双引号。如果你从 CSV 复制出来的内容带了引号,分列时引号会自动去掉。

预览区会实时显示切完的样子,切完是不是你要的列数,在第 2 步就能看出来,不用等到最后。

第 3 步才是精髓:三件必须做的设置

一、给每一列指定数据类型(最重要)

选中预览区里的某一列,上面"列的数据格式"有四个选项:

选项
作用
常规
默认。Excel 自己猜类型
文本
强制当文字存,一个字符都不动
日期
YMD / MDY / DMY 三种顺序可选
不导入此列(跳过)
这一列直接扔掉

"常规"是事故源头。 因为选常规时 Excel 会按内容猜类型,于是:

  • 前导零丢失
    :工号 00123 会被存成数字 123,事后你把它转成文本,得到的是 123,那两个零永久没了
  • 长数字变科学计数
    :18 位身份证被当数字处理,直接变 1.23E+17,而且第 16 位起真实变成 000,这是值被改了,不是显示变了
  • 日期被乱猜
    :1-10 可能变成 1 月 10 日或 10 月 1 日,取决于你系统区域设置

所以只要那一列是标识符——工号、身份证、手机号、银行卡、统一社会信用代码、带前导零的编码——必须手动把它设成"文本"再点完成。 顺序反了就救不回来,只能重新导一次原始数据。

反过来,如果那一列是日期,就该在"列的数据格式"里选日期并挑对顺序。这正是前期咱们学的用它统一乱日期格式的原理:分列的日期选项能把文本日期真正转成 Excel 日期序列值。

数值列选常规就行;不要的列(比如重复的序号列)选"不导入此列(跳过)",省得拆完还要删列。

二、"目标区域"决定原文能不能留住

默认"目标"填的是这一列自己的第一个格子(比如 $A$1),意思是拆完直接盖回原列,原来那串"张三-13800138000-上海"就没了。

想保留原文,把目标改成右边的空白列:

目标:$B$1

A 列原样不动,拆出来的列从 B 开始往右铺。这样核对、返工都方便。

三、目标区域右边必须留够空列

分列不会提醒你"要覆盖数据了",它直接把右边同宽度的列全部盖掉。

拆 4 列,目标设成 B1,那 B、C、D、E 四列原有内容全部消失。执行前先看右边是不是空的,或者干脆把这一列复制到最右边的空白区域再做分列,这是最稳的做法。

一个实操顺序(照做就行)

  1. 选中要拆的列 → 右键复制 → 在最右边空白列粘贴一份。原文留底,出错不慌。
  2. 数据 → 分列 → 第 1 步选"分隔符号"
  3. 第 2 步勾对分隔符,横杠数据就填"其他 -“,勾上"连续分隔符号视为单个处理”,看预览列数对不对
  4. 第 3 步逐个点预览列设类型:标识符类设文本、日期列设日期、不要的列选跳过
  5. 目标区域填一个右边全空的起始格
  6. 完成。检查一遍前导零、身份证位数有没有丢

第 4 步是唯一不能省的一步,前面讲的两类数据事故全靠它挡。

什么时候该用分列,什么时候不该

需求
用什么
有统一分隔符,一次性拆完就走
分列
字段定长、没有分隔符
分列
(固定宽度)
拆出来要保住前导零/长数字
分列
,且第 3 步设文本
分隔符不固定、但规律肉眼清晰
Ctrl+E 快速填充
数据会持续新增,要自动拆
公式:TEXTSPLIT / MID+FIND
字段内容本身含分隔符(地址里也有横杠)
公式或正则,别用分列
一行有多组括号要删
正则(前期讲过,分列也帮不上)

分列和 Ctrl+E 的区别,本质是这样:分列按分隔符机械切分,切完的每一列类型由你指定;Ctrl+E 是模仿你给的样例,样例给得不够它就可能猜错,而且它输出的永远是文本,想拿到真日期还得再转一道。

拆完的数据要参与计算、要做透视,优先分列;只是临时把字挪个位置,Ctrl+E 更快。

五个容易踩的坑

第一,对含公式的列做分列。 分列作用于显示值,点完成之后这一列的公式全部消失,变成拆出来的静态文本。要拆公式列的结果,先复制 → 选择性粘贴 → 值,再分列。

第二,忘了把标识列设成文本。 前导零和身份证的坑,上面用了一整节讲,因为它真的救不回来。

第三,目标区域设得太靠左,盖掉了别的数据。 分列不弹窗警告覆盖,直接把右侧同宽度的列清掉。

第四,想一次拆好几列。 分列一次只能处理一列。有三列要拆就选区、跑三次,没有批量入口。

第五,分隔符全角半角不一致。 数据里是全角横杠 -,你在"其他"里填半角 -,一条都切不开。拿不准就先把数据里的横杠复制一个,直接粘到"其他"框里,别手打。

今天的总结

分列三步:

第一步选模式(有分隔符用"分隔符号",字段等宽用"固定宽度");

第二步定分隔符(多个可同勾、连续分隔符勾单个处理、双引号识别别动);

第三步定类型——这才是重点:标识列设文本保前导零和长数字,日期列设日期,废列选跳过,目标区域挑右边全空的位置。

顺手记住一句和它相关的原则:拆出来是标识符的数据,宁可当文本也别当数字,因为前导零一旦丢了,靠事后转格式是恢复不出来的,只能回源头重导。

下次再拿到"啥都挤在一列"的数据,别急着复制粘贴。数据 → 分列,走到第 3 步把类型一个个点好,四列入手。


——E晓分享,共同进步——

回首页,搜索你想要的文档

在首页搜索框输入关键词,快速定位资料

文库首页搜索指引 立即搜索

相关学习资料