感谢 CONCAT ，你拯救了 Excel 的文本处理能力

《Excel 版滑板鞋》

有些事我都已忘记。

但我现在还记得，在一个晚上，

我的同事问我，今天怎么特开心？

我说在我的 Excel 中，有一个 CONCAT，

与众不同最时尚，用起来肯定棒！

在小花改编的《Excel 滑板鞋》中，提到了一个函数，CONCAT。

它是 OFFICE 2019 等高级版本中，才破笋而出的「后浪」。

直到落笔前，小花才想到一句话来形容这个「超级新人」：CONCAT 拯救了 Excel 的文本处理能力。

稍加搜索，便发现目前的 Excel 教程对它都是浅尝辄止，而小花一贯毫无保留。

前方烧脑，请自备核桃！

PS：如果觉得文中有些公式太难学不会，会套用就好。

合并再多文本，都只是基操！

让我们先看看，函数胖子 CONCATENATE 是如何完成文本连接的。（名字这么长，不是胖是什么？）

两个字评价，「鸡肋」！难怪 CONCATENATE 函数很快被「&」连接符取而代之。

痛定思痛，疯狂瘦身后，胖子变型男，CONCAT 函数闪亮登场！

它的功能是连接列表或文本字符串区域，只有一个参数，即：=CONCAT（文本区域）

相对于 CONCATENATE 函数或「&」连接符的逐一连接，CONCAT 的效率提升可是指数级别的。看下图：

小花：哎呦，就算你们这么夸我，我也不会开心的！哈哈哈！

粉丝：我们才没夸你，别 YY！

当然，除了合并行列，多行多列区域文本合并，CONCAT 也是轻松搞定！

按条件合并文本，也是妥妥的！

还在使用 OFFICE 2016 版及以下的朋友，按部门合并姓名这类问题，你是如何解决的？

排序 + IF 法，VLOOKUP 法还是 PQ 法……（这些知识点，你会吗？）

CONCAT 为我们提供了全新的便捷思路。公式如下：

{=CONCAT(IF(A2:A7=D2,B2:B7&"、",""))}

公式说明：

使用 IF 函数进行条件判断，对满足「部门」条件的人员，返回其姓名 &"、"；不满足条件的，返回空，从而返回 N 个「姓名、」值。

再使用 CONCAT 将这些文本连接起来。

混合文本提取数字，这才是最优解！

没有 CONCAT 函数之前，为了从混合文本中提取数字，特别是不连续数字，各路大神真是绞尽脑汁、肝脑涂地。

什么 SUM+LARGE、什么 LOOKUP+MID，各种神级嵌套、各种数据逻辑。

终于，这一切在 CONCAT 问世后，宣告下架。

提取数字这事儿，CONCAT 才是最优解。公式如下：

{=CONCAT(IFERROR(--MID($A2,ROW($1:$100),1),""))}

公式说明：

使用 MID+ROW 组合，将混合文本中的每一个字符都单独提取出来，然后使用双负号进行运算。

此时，数字返回其本身，非数字返回错误#VALUE!，IFERROR 随即将错误值转化为空。

于是 A2 中数字被保留下来，其余全部被替换为空，CONCAT 将它们连接起来，即可完成对数字的提取。

提取不重复数字难题，照样拿下

Q：语文是数学老师教的，是一种什么体验？

A：「1 去 23 里，烟村 45 家」这句诗里用到几个数字？

从混合文本中识别用到的数字，这是一个充满趣味和挑战的 Excel 问题。

OFFICE 2016 版以前的 Excel 用户中要完成这项任务可谓困难之极，或许仰仗 PQ 之力尚有一线希望。

但在 CONCAT 铁蹄之下，没有征服不了的「文本处理」大山！

{=CONCAT(IF(ISNUMBER(FIND(ROW($1:$10)-1,$A2)),(ROW($1:$10)-1),""))}

公式说明：

❶ ROW ($1:$10)-1 返回从 0-9 这十个阿拉伯数字，再通过 FIND 函数查找这些数字在 B2 中出现的位置。

如果 A2 单元格包含该数字，则返回位置序数值，否则返回#VALUE!。

❷ 利用 ISNUMBER 对 FIND 的返回值进行判断，使得序数值和#VALUE! 的有序数组变为一组逻辑值，逻辑为 TRUE 代表 A2 中包含该数字，FALSE 则相反。

❸ 随后 IF 函数发挥作用，TRUE 返回对应的数字，FALSE 返回空。

❹ 最后 CONCAT 收尾，将这些数字连接起来。

看到这，实战派不禁要问，这个公式在工作中似乎无用武之地？

错！它大有可为。

实战案例：根据部门人员名单和考勤登记名单，自动计算缺勤人员名单。

{=CONCAT(IF(ISNUMBER(FIND(TRIM(MID(SUBSTITUTE(A2,"、",REPT(" ",99)),ROW($1:$100)*99-98,99)),B2)),"",TRIM(MID(SUBSTITUTE(A2,"、",REPT(" ",99)),ROW($1:$100)*99-98,99))&"、"))}

▲ 左右滑动查看

公式简要说明：

我们通过 SUBSTITUTE 函数将姓名间的顿号替换为 99 个空格，使得 99 个空格和一个姓名形成一组独特的字符串，每组独特字符串仅包含一个姓名。

随后我们像提取不重复数字的做法一样，将 99 个字符作为一个单位并去除空格进行比对，只是返回值做了调换，包含返回空，不包含返回姓名 &"、"。

最后，再使用 CONCAT 将它们连接起来，形成缺勤名单。

其他有趣用法，了解一下！

在尝试使用 CONCAT 完成上述几个高能用法的过程中，小花意外挖掘出了 CONCAT 的两个有趣用法。

难以抑制分享的冲动，权当烧坏各位小伙伴脑瓜的最后一把稻草，不要太感谢我哦！

▋趣味运用 ❶：倒背如流

将文本逐一颠倒过来，倒序排列，这样诗情画意、才气盎然的 CONCAT，你喜欢吗？

{=CONCAT(MID(A2,101-ROW($1:$100),1))}

公式说明：

原理很简单，使用 101-ROW ($1:$100) 构建一个从 100 至 1 的逆序数组，于是 MID 就从最后一个字符开始，逐一提取字符，再由 CONCAT 组合即可。

补充一点，如果 MID 的第二个参数大于文本的长度，MID 截取的结果本身就为空，直接连接即可。

▋趣味运用 ❷：慧眼识珠

小屁孩秀下限造出来的各色错别字，绝对是语文老师和家长的梦魇。

还好有 CONCAT 帮忙纠错，妈妈再也不用担心我的眼睛。

{=CONCAT(IF(MID(B2,ROW($1:$100),1)<>MID(A2,ROW($1:$100),1),MID(B2,ROW($1:$100),1),""))}

▲ 左右滑动查看

公式说明：

使用 MID+ROW 函数分别从 A2 和 B2 中将字符逐一提取出来，一一比对，错误则返回 B2 中对应的字符，正确返回空。

最后由 CONCAT 函数将这些错别字都连接起来即可。

此篇文章，是否刷新了你对 CONCAT 函数的认知？

其实，CONCAT 的孪生加强版函数 TEXTJOIN 也能够完成上述操作，甚至做得更好，学有余力的小伙伴不妨尝试一下！

本文来自微信公众号：秋叶 Excel （ID：excel100），作者：小花