Ragic 博客
企业电子化的专家 Ragic 教你如何利用各种软件、
云服务让公司快速升级!
加入 Ragic 企业电子化的行列!
云工作术
各类应用演示
案例故事
逃离恶梦
关于 Ragic
Facebook Twitter YouTube
云数据库
博客
关于Ragic
云工作术
各类应用演示
案例故事
逃离恶梦
关于 Ragic

如何将 Excel 挤在单一单元格的数据分割成多格(或将多字段并成单一字段)?

作者:Lillian Huang

经手大量数据的人有时会遇到的问题:从其他地方下载 / 汇入 / 拷贝到 Excel 的一批数据,格式和需要的不一样,得自己整理。可能是一坨数据都挤在同一格里面,需要把同一个字段里的不同信息分拆到不同单元格,或是反过来,需要把一批数据的多个字段值,全都一起归并到同一个字段。

这时候,不用一一笔数据拷贝贴上、归并单元格。很多人知道可以利用现成的功能:数据剖析(一拆多)和公式(多并一),来快速处理多笔数据,但有时没办法快速判断“怎么用”,特别是遇到换行等特殊状况时,会需要用点小技巧。

这里特别整理出各种情境的对应解法,可以参阅下方表格,点击对应的超链接。

(注:如果你是 Ragic 的订户,要将 Excel 汇入 Ragic 前有时也需要这样的数据整理技巧。如果是已经存放在 Ragic 的数据,想知道怎么在 Ragic 做到这些事的话,可以查看这个部分的说明。

需求图符方法相关说明
快速拆字段(一拆多)数据剖析1.固定宽度怎么拆
2.空格或特定符号区隔怎么拆
3.不太规则的情况怎么拆
4.拆成多列
5.单元格内多行(换行)怎么拆
快速归并字段(多并一)主要是各种公式1.单纯多并一
2.抽取部分内容多并一(去字尾)
3.多并一且在单元格内换行

快速拆分字段(一拆多)

例如,原本一批数据的某个字段值是“姓名”像“王大明”,现在需要把它分拆成一栏“姓”一栏“名”变成“王”和“大明”:

又例如拿到一组全都挤在一栏的“地址”信息,希望依照邮政编码、县市乡镇等分成一栏一栏的:

这在 Excel 最简便的方法就是利用数据页面签下的数据剖析来处理。

数据剖析

只要你的一批数据可以整理出一定的“分栏规则”,就可以用数据剖析把一个单元格一刀切成好几块。

最简单可以直接适用的规则是:① 要切成固定宽度(不同数据每一段要切的都一样长,字符数一样),或者 ② 字段值有以空格或特定符号来分隔。所以先来说这部分。

① 固定宽度/固定字符

如果你想要“切”的数据,切分点右方每一段的字数都是相同的(例如把地址切成邮政编码、县市乡镇、其余地址,其中邮政编码固定是 3 码、县市乡镇固定是三个国字),那它就是固定宽度/固定字符的情况,可以直接使用“数据剖析”来切数据。

方法为:框选要拆分的字段后,到数据 > 数据剖析 > 选“固定宽度” > 依系统指示在画面上切出分割线,完成步骤即可。如果希望指定把切好的字段放在其他地方,就点“目的地”来另外框选位置。

② 字段值有以空格或特定符号来分隔

“106 台北市 大安区 南昌路 二段 222号 ”、“106,台北市,大安区,南昌路,二段,222号 ”“106台北市大安区南昌路二段_222号 ”“106/台北市/大安区/南昌路/二段/222号 ”......这些都可以切成“106”“台北市”“大安区”“南昌路”“二段”“222号 ”没问题。

不过不限于这些符号,其实只要是一个特定的字符都可以,例如“106✦台北市✦大安区✦南昌路✦二段 ✦222号”只要有指定用✦来区隔,或是“106隔台北市隔大安区隔南昌路隔二段隔222号”指定用“隔”来区分,就都可以得出“106”“台北市”“大安区”“南昌路”“二段”“222号 ”。

方法为:框选要拆分的字段后,到数据 > 数据剖析 > 选“分隔符号” > 选择你的分隔符号并完成步骤即可。(可参阅上图。如果希望指定把切好的字段放在其他地方,就点“目的地”来另外框选位置。)

选分隔符号时,如果是上面提到的特殊分隔符号,就勾“其他”并填入该字符即可:

如果同时有两种分隔符号(例如空格与逗号),只要两个都一起勾选就可以了喔!

这里还有两个“特例”可以讲一下,以下例子虽然分隔符号不是那么“规则”或直观,但还是可以用的:

特例 ①:分隔符号没那么“整齐”?

首先,假如我手上有一批地址数据要切得很细,每段宽度不一(例如前面例子的邮政编码有的三码有的五码;或更常见的路名长度不一、“区”有的一个字有的两个字),不能用固定宽度来切,但也没有空格、逗号等区隔符号,该怎么办?

用“查找与替换”制造区隔字符

此时仔细研究一下,会发现我想要分段的东西还是有规则可循的,例如邮政编码跟地址之间一定一边是数字一边是字、其他的区隔多半可以从“县”“市”“乡”“镇”“区”关键字看出。

利用这些规则,搭配 Excel 的“查找与替换”功能,把我们想要切割的地方制造出“,”之类的区隔字符,之后就可以快速利用前面的数据剖析功能了。例如下图:你可以用“县,”替换“县”,“市,”替换“市”,“乡,”替换“乡”,“镇,”替换“镇”... 如果邮政编码跟地址中间没空格,一样可以用“0,”替换“0”,以此类推入。

连续分隔符号视为单一处理

另外一个比较单纯的情形:例如假设我的数据每一段都是用双引号" "括起来,要做数据剖析时,会发现两段之间因为有两个分隔符号,会被额外切出一个空栏。此时只要在设置画面勾选“连续分隔符号视为单一处理”即可:

特例 ②:不想拆成并行的多字段,想拆成希望它们分在不同列?

例如你手上有的是一串名字,它们有用空格区分,但都挤在同一个字段,你最终希望一个名字放在一个单元格,且每一个名字是垂直往下一列一列排的像这样:

那首先还是可以先用数据剖析把它拆成多字段。接着,利用 Excel 的转置功能从栏转置(旋转)到列就可以了,方法为:先选取这些单元格之后右键拷贝,接着到要贴上的地方单击“选择性贴上”并选“转置”。

特例 ③:挤在同一个单元格里的多行数据怎么拆成多列/多个单元格?(换行符号分隔)

其实在 Excel,“换行”这个动作本身是可以被识别或转换成某个区隔符号的,把它识别出来、或转换出来,就可以当成一个“分隔符号”来做数据剖析了。(解决这点,接下来有需要的话可以用跟上面一样的方法,用“转置”来将栏换成列)

在数据剖析向导里直接填入快速键“Ctrl+j”

很常见的方法是利用代表“换行符号”的快速键 Ctrl+j ,在数据剖析向导里直接打 Ctrl+j:

很多情况下画面上虽然仍然是空白(肉眼看不到你打的 Ctrl+j ),但你可以看到已经把不同行的数据区隔开了。

利用“查找与替换”将“Ctrl+j”替换成其他分隔符号例如“,”

一样就是在“查找与替换”时,查找“Ctrl+j”,用“,”替换。替换完之后就可以用这个肉眼可见的分隔符号来做操作了,这是很多人用的方法。

其他方式:利用 CHAR(10) 公式

但如果你跟网编一样,不确定是因为 Excel 版本还是 Mac 计算机的关系,用了“Ctrl+j”或是其他任何江湖传言的换行符号,都没有任何改变,可以来试试另一个方法: 用 CHAR(10) 公式来找。

CHAR 这个公式的意思是“取得计算机字符编码代表的值”,而 10 是在 Mac 跟 Windows 计算机里都可以代表“换行”的编码,所以 CHAR(10) 就是换行符号的意思。

可以利用一个效果跟“查找与替换”类似的公式: SUBSTITUTE 公式,来做到把换行符号代换成其他肉眼可见符号的方法。

SUBSTITUTE 在 Excel 的用法是这样:

=SUBSTITUTE(参照字段值,参照字段中要被代换的值,要把被代换的值换成什么值),例如“一百三十元”要换成“一百三十圆”,那就是SUBSTITUTE(参照字段值,元,圆)。

因此如果要以“,”替换换行符号,公式就是这样写:

用公式取得有区隔符号的值后再做一次数据剖析就可以了。

如果真的找不出个规则?

计算机、自动化处理批次数据这种事情就是需要有个规则,所以真的理不出一个规则的话就真的就没办法了,可能还是得从源头数据着手,让数据从源头就用比较好处理的方式呈现、汇出。

快速归并字段(多并一)

有时候你可能有跟“分拆字段”相反的需求:要把多个字段归并成一个,例如手上有“姓”和“名”要合成“姓名”,手上有邮政编码、县市乡镇等,要组合成一个完整的地址字段,或是要把姓名、地址等字段集结成单一个出货备注字段。

这些在 Excel 大致上可以用字符串公式搭配一些变化版达成,如下。

① 单纯多并一

如果我手上拿到的数据是这样:“106”“台北市”“大安区”“南昌路二段222号 ”,想要集成成单一单元格的完整地址“106 台北市大安区南昌路 二段222号 ”,这是最单纯的需求,可以用 Excel 的字符串公式 & 或是归并多个字符串的公式 CONCAT 或 CONCATENATE 来做。

② 抽取部分内容多并一(去字尾)

举例来说,“106”“"台北市"”“"大安区"”“"南昌路二段222号" ”,想要集成成没有双引号区隔的单一单元格完整地址“106 台北市大安区南昌路二段222号 ”,那么可以利用“抽取部分字符串”的公式 MID 抽取去掉头尾的字符串,再利用前面字符串相加的公式串起来即可。

*要抽取的字符串有固定字符长度的话,MID 公式里填入所需的字符长度即可;没有固定字符长度的话可以找出规律,例如下图“其余地址”这个字段要取的字符数会随着地址长短而有所不同,但确定的是都要取"之后到“号”这个字之间的字符数,因此搭配 SEARCH 公式 “SEARCH("号",D2)-1”取得所要的数字填入。

③ 多并一且在单元格内换行

跟“单纯多并一”的情况相比,只需要在字符串公式中插入换行符号。

前面有讲到,在 Excel 公式里要系统性的使用换行符号可以用 CHAR(10) 来做,因此只要在 =(单元格1&单元格2&单元格3)这样的公式中,插入 CHAR(10) 像这样: =(单元格1&CHAR(10)&单元格2&CHAR(10)&单元格3),另外记得在该单元格上的“常用”页面签勾选“自动换行”图符,就可以了。

同场加映:怎么在 Ragic 拆字段或归并字段?

如果你是 Ragic 订户,偶尔会拿到一些不太符合原本 Ragic 表单格式的数据,你可以考虑利用上面讲的 Excel 功能拆分或归并单元格,清整数据之后再汇入 Ragic,就不用在 Ragic 为了整顿数据创建多余字段,可以省很多力。

不过如果你是固定会需要这样拆字段/归并字段之后汇入 Ragic ,评估有需要的话,以上需求也可以在 Ragic 用公式来做。 Ragic 的公式好处是:逻辑类似 Excel 、好上手,但一批数据只要套用一次公式在字段首部即可,省力且不易在编辑数据/拷贝字段值时被意外错改。

拆分字段

Ragic 目前没有 Excel 的“数据剖析”功能,因此一次性的字段拆分其实还是比较建议在 Excel 整理。不过如果需要系统性在 Ragic 做这种事的话,Ragic 也支持各种字符串抽取的公式: MID, LEFT, RIGHT 等取出部分字符串的公式,以及“找出某个字符在字符串中位置”的公式:FIND(无论该字符是中文或英文,在 Ragic 都可以使用 FIND,不需要特别使用 SEARCH,例如FIND('吴',A1) 就是找出 A1 字段中“吴”是第几个字)。

因此,再以前面的地址为例,假如手上有的是完整地址,要拆分成不同字段的话可以像这样:

归并字段

Ragic 也支持“字符串相加”的公式,但逻辑比 Excel 更简单一些,不用 & 而是用加号“+”。只要是非数字字段使用“+”,例如“A1+A2”,就等同于 Excel “=A1&A2”的意思。邮政编码、县市等组成完整地址字段的例子,公式就会像这样:

需要抽取部分字符串相加时,由于如同上面所说,Ragic 也支持各种抽取字符串的公式,直接搭配抽取字符串与 + 一起使用即可。

至于“搭配换行”的部分,Ragic 采用的是换行符号 "\r\n",可以直接参阅这篇文章的说明来轻松达成订单内备注换行等功能。

标签: Excel

归类: 云工作术, 表格技巧

博客背后使用 Ragic! : 最强大的 No Code 企业电子化工具
把数据放在Excel上不只是拖累团队的行政效率,他也很容易出错并且无法进行任何内控。
当您的团队成长时,使用Excel管理数据就会越来越痛苦。
创建你们的第一个云数据库!

马上登记
免费试用 Ragic!

用 Google 帐号登记

立即科技 Ragic, Inc.
02-7728-8692
台北市中正区南昌路二段81号9楼