如何高效处理文本数据？Excel三大神器详解-CONCATENATE、LENMIDLEFTRIGHT

ChatGPT与Excel 5个月前 lida

25 0 0

文章主题：文本处理, Excel函数, CONCATENATE, LEN

以下内容主要是涉及到如何处理文本的Excel函数。

一、CONCATENATE函数

🌟掌握英语连接神器！🔍认知新词”concatenate”，它就像胶水，能将各种文本片段无缝拼接！💪只需几个简单的步骤，就能让文字如丝般流畅——`=CONCATENATE(text1, text2, text3…)`，这个公式就是魔法棒，轻松合并任意数量的字符串！👀想象一下，你想要创建一个引人入胜的故事文案，每个部分都是独立的宝石。使用CONCATENATE，只需轻轻一挥，它们就熠熠生辉，连成一篇华丽篇章！✨别忘了，SEO友好型的文本是王道，像”concatenate函数详解”这样的关键词，能帮你吸引搜索引擎的目光，让你的作品更容易被发现。🌍快行动起来，提升你的写作技巧，让信息流动更高效！📚💪

如何高效处理文本数据？Excel三大神器详解-CONCATENATE、LENMIDLEFTRIGHT

按照相好需要合并单元格的先后顺序，分别点击目标单元格，并用英文逗号隔开。

原文改写：若需锁定特定文本位置，如图中的”and”，选中后轻按F4键，会自动在其前后添加美元符号($)，明确标识J8单元格内的”and”始终与”text2″相绑定，确保内容固定不动。🌟✨SEO优化：锁定”and”位置秘籍💪-$符号标记，J8单元格保证”text2″不变🚀

函数输入完毕后，将第四列第一行的单元格下拉扩展至2-4行的效果如下图。因为第三列的第二行到第四行为空，如果不固定“and”，第四列的第二行到第四行将不会出现“and”（正如下图所示，第一列第三行以及第二列第四行为空值，所以在第四列第三行和第四行中，and前后分别没有任何文本）。

函数CONCATENATE的相似函数为&，公式为“=text1&text2&text3….”，这里同样可以点击F4保持某个变量位置不变。函数CONCATENATE和&常常与其他函数套用。

二、LEN MID LEFT RIGHT函数

1、LEN函数

计算文本长度。相似函数包括LENS——计算字符长度，英文一个字母是一个字符，中文一个汉字是两个字符。

公式：LEN(文本)

举例，LEN函数计算文本长度

上图示例中单元格的文本长度为25。

LEN函数也常与其他函数套用。

2、MID LEFT RIGHT函数

从各个位置提取文本，三个函数的公式如下：

MID(需要提取的单元格和文本, 从哪一个字符开始, 提取多长)

LEFT(需要提取的单元格和文本, 提取多长)

RIGHT(需要提取的单元格和文本, 提取多长)

下图为在某交友网站爬取的数据，可以看出数据的左边为用户的昵称，最右边为用户的身高数值，并且可以确定是由三个数字组成，因此可以使用RIGHT函数提取身高数据。首先点击需要提取的单元格和文本——单元格A2。

然后提取三个数字，输入3。

点回车确定，把原始数据中所有的身高数据全部提取出来。

如何提取原始数据中的用户昵称呢？这就需要Excel函数之间的相互套用了。

用LEFT函数从最左侧提取原始数据的用户昵称，点击需要提取的单元格和文本——单元格A2。

接下来就该确定“提取多长”了，我们利用LEN函数确定整个文本的长度，减去身高数据的长度3（注意：根据对原始数据进行初步分析，我们可以判断身高数据的长度是一个确定值）。

因此，整个公式为”=LEFT(A2, LEN(A2)-3)”，确定得到原始数据的昵称

以上介绍的这种方法要比在

【Excel】：数据工具选项卡

中提到的快速填充功能要准确得多，绝对不会出错，无论遇到各种奇怪符号、空格还是其他字符都能准确的提取到用户昵称。

三、SUBSTITUTE REPLACE FIND函数

1、FIND函数

返回一个字符串在另一个字符串的起始位置，因此FIND函数最后返回的是一个位置的值，即一个数字。

函数公式为：FIND(需要查找的文本, 原来的文本, [可选搜索起始位置])

FIND函数也经常与其他函数套用。

以下图在豆瓣上爬取的电影数据为例，在原始数据中找到刘德华的电影。在Excel中使用FIND函数，首先输入需要查找的文本“刘德华”（在公式中使用英文引号），然后点选原来的文本——单元格C2。

回车得到结果，并对结果进行降序排列。可以看到，FIND函数返回值为2，说明这些电影是刘德华作为主演的电影（主演在演员表的位置非常靠前，值为2）；FIND函数返回值为14或89，说明这些事刘德华作为友情客串出演的电影或者是多位明星共同出演的电影；FIND函数返回值为错误提示#VALUE!，说明这些是没有刘德华参演的电影，见下图。

因此，通过配合爬取到电影的豆瓣评分、电影名称、上映日期等其他数据，可以分析出某个明星作为主演的电影评分大致走向或曲线，或者对比出这个明星每个时期所主演的电影是好还是差等等。

本文第二部分提到的MID函数可以与FIND函数进行套用。

MID函数的公式为：MID(需要提取的单元格和文本, 从哪一个字符开始, 提取多长)

还是以电影数据为例，我们利用MID函数提取原始数据电影演员表里的演员“刘德华”这三个字。根据MID函数的公式，需要提取的单元格和文本还是原始数据C2。如何确定从哪一个字符开始呢？这就需要套用FIND函数来确定了，即上文中FIND函数确定的起始位置的值。最后，输入“刘德华”三个字的文本长度3，如下图。

因此，例子中整个公式为“=MID(C2, FIND(“刘德华”, C2), 3)”。

刘德华参演的电影全部提取出来了，如果没有刘德华，函数的返回值依然为错误提示#VALUE!。

2、SUBSTITUTE函数和REPLACE函数

两个函数的英文含义虽然中文翻译过来都是“替换”，但实际上两个函数在逻辑上依然有一些区别，以下将分别详细说明。

SUBSTITUTE函数是将部分字符替换成新的字符，公式为：SUBSTITUTE(原来的文字, 老的文字, 需要替换的新文字, [可选]替换第几个)。

还是用豆瓣电影的数据举例说明，将原始数据中的“葛优”替换为“葛大爷”，原来的文字是原始数据单元格A2，老的文字是葛优（在公式中使用英文引号，下同），需要替换的新文字是葛大爷，最后替换第几个是可选项，意思是如果原始数据中如果出现了多个“葛优”需要替换第几个，默认（即忽略掉）表示全部替换。

因此，整个公式为“=SUBSTITUTE(A2, “葛优”, “葛大爷”)”。回车后下拉，所有数据中的“葛优”均被替换为“葛大爷”。

REPLACE函数是将部分字符用另一份字符替换，公式为：REPLACE(原来的文字, 开始于第几个字, 需要几个字, 替换的新的文字)。

大家可以发现REPLACE函数中的变量为开始于第几个字，所以我们又需要跟FIND函数套用来获得这个变量的值。

同样的数据，还是将原始数据中的“葛优”替换为“葛大爷”。原来的文字是原始数据单元格A2，开始于第几个字需要嵌套函数FIND（为了方便区分，这里选择A3作为FIND函数的原始数据单元格，实际中选择A列中包含原始数据的任意单元格均可），由于嵌套函数非常容易出错，一般可以先选中写好的函数（如下图）。

点击F9进行测试，返回值为2，正确。

接下来，我们继续输入公式：需要替换几个字——“葛优”，所以为2；替换的新的文字——“葛大爷”。