在做数据分析的过程中,有时我们要整理文本类型的数据,本文总结了一些实用的方法,既包括手工操作,也有常用的函数套路供大家参考,都可以提升工作效率。另外你掌握了 Excel 函数公式的处理思路后,即使你是使用 PQ、VBA 编程的时候,思路也是同样适用的。
快速填充
快速填充,快捷键:Ctrl + E首先我们要了解的是 Excel 2013 版本后的快速填充
功能,当我们要快速处理一些临时任务时,用它的效率非常高。
看个示例:
快速填充,快捷键:Ctrl + E很多人看到这里会问中文姓名可以吗?试试看!
快速填充-中文姓名分离可以看到,会出问题!
英文姓名可以分拆,并不是因为 Excel 智能,而是英文姓名是用空格分隔的,Excel 快速填充功能只需要识别你是要提取空格前还是空格后即可。
中文姓名组合丰富,人也是凭借记忆去分辨姓、名的,但 Excel 里并没有中国姓氏的数据库,所以自然就不能识别出来;你输入的是张
时,它识别成你要提取文本中的第一个字符,你输入三
时,它以为你要提取除第一个字符外的其他字符。
如果你的姓名数据中都是纯粹的两个字、或者三个字的,而且姓的字数是一样的,那么 Excel 也是能识别出来的。
但对于规律性比较明显的一些字符串整理,快速填充
功能已经很智能了:
使用快速填充
功能的一些注意事项:
模式识别:确保提供的示例数据足够明确和具有代表性,以便 Excel 可以准确识别和推断模式。如果示例数据不足或存在复杂性,Excel 可能无法正确填充数据。 连续区域:要确保是在连续或相邻的列中去操作,如果中间有空列 Excel 不会帮你识别,除非是表格类型。 校正错误:如果 Excel 没有正确推断出数据,可以手动更正填充的结果,Excel 会再次根据手动输入的数据调整填充的模式。
快速填充
比较适合处理临时性的简单任务,它每次都是需要你手工给示例数据的,无法实现自动化调用。如果需要自动化,在 Excel 工作表中,就要用函数去写公式了,以下介绍一些常用套路。
基础文本函数
以下这些都是最基本的文本字符处理函数,都是必须要熟练掌握的:
LEFT
= LEFT("数据化管理", 2)
提取 "数据化管理" 左边的 2 个字符,计算结果:"数据
"
RIGHT
= RIGHT("数据化管理", 2)
提取 "数据化管理" 右边的 2 个字符,计算结果:"管理
"
MID
= MID("数据化管理", 3, 2)
从 "数据化管理" 的第 3 个字符开始,往右提取 2 个字符计算结果:"化管
"
LEN
= LEN("数据化管理")
"数据化管理" 这个文本有几个字符?计算结果:5
FIND
= FIND("化", "数据化管理")
"化" 是 "数据化管理" 的第几个字符?计算结果:3
SEARCH
= SEARCH("化", "数据化管理")
"化" 是 "数据化管理" 的第几个字符?计算结果:3
FIND
和SEARCH
在搜索中文字符常量时可以混用,主要区别FIND
是区分英文大小写,而SEARCH
则不区分英文大小写;FIND
不支持通配符,SEARCH
支持。
SUBSTITUTE
= SUBSTITUTE("数据化管力", "力", "理")
把 "数据化管力" 中的 "力" 替换成 "理",计算结果:"数据化管理
"
以上 6 个就是基础的文本处理函数,语法也很清楚,大家一个个看的话,相信是肯定都能明白的。
但 Excel 公式的运用,重难点是在于函数的嵌套。因为函数的嵌套其实就是算法,写算法就要考你的逻辑理解能力了。对于文本的整理,有一些常用的套路,以下就是结合具体文本处理场景的一些经典嵌套公式(以下将按由易到难的顺序排列,记得看完)
,如果你都能明白了,那就能成为文本处理高手了~
常用文本处理套路
01 删除固定长度的文本
删除左边 1 个字符
A1 单元格 = "?数据化管理"
= RIGHT(A1, LEN(A1) - 1)
删除 "?数据化管理" 中的一位字符?
,计算结果:"数据化管理
"
删除右边 1 个字符
A1 单元格 = "数据化管理?"
= LEFT(A1, LEN(A1) - 1)
删除 "?数据化管理" 中最右边的一位字符?
,计算结果:"数据化管理
"
02 基于分隔符提取
A1 单元格 = "martin.lau@foxmail.com"
提取分隔符之前的文本
= LEFT(A1, FIND("@", A1) - LEN("@"))
计算结果:"martin.lau
"
提取分隔符之后的文本
= RIGHT(A1, LEN(A1) - FIND("@", A1))
计算结果:"foxmail.com
"
A1 单元格的值是"数据-化-管理"
提取分隔符之间的文本
= MID(A1,
FIND(左分隔符, A1) + LEN(左分隔符),
FIND(右分隔符, A1, FIND(左分隔符, A1)) - FIND(左分隔符, A1) - LEN(左分隔符)
)
如果想提取邮箱地址 "martin.lau@
foxmail.
com" 中 @
和 .
之间的文本:
= MID(A1,
FIND("@", A1) + LEN("@"),
FIND(".", A1, FIND("@", A1)) - FIND("@", A1) - LEN("@")
)
计算结果:"foxmail
"
扩展:一切字符都是可以是分隔符,照样套模板使用即可
例如字符串 "@数据化管理" ,我想提取 "@数据" 和 "理" 之间的字符,就这样写:
= MID(A1,
FIND("@数据", A1) + LEN("@数据"),
FIND("理", A1, FIND("@数据", A1)) - FIND("@数据", A1) - LEN("@数据")
)
计算结果:"化管
"
03 提取连续的中文
A1 单元格 = "数据化管理 sjhgl12345"
当字符串格式是“连续中文 + 连续(英文/数字)”,可以用下面的公式,提取左侧连续的中文:
= LEFT(A1, LENB(A1) - LEN(A1))
计算结果:"数据化管理
"
这个方法的原理是利用 LENB
函数和 LEN
函数的差值来确定文本中的中文字符数量。在大多数情况下,一个中文字符占用的字节数是 2,而一个非中文字符(如英文字母或数字)占用的字节数是 1。通过计算 LENB(A1) - LEN(A1)
,可以得到左边连续中文字符的字节数。然后使用 LEFT
函数截取相应的字符。
同理如果字符串的格式是 “连续(英文/数字) + 连续中文”,可以用下面的公式,提取右侧连续的中文:
= RIGHT(A1, LENB(A1) - LEN(A1))
但是以上这种写法遇到更复杂的情况时, 例如 A1 = "数据化管理 sjhgl12345 我是中文字符",就要换一种实现方式了:
= LEFT(A1,
MIN(
IF(
UNICODE(MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1)) < 128,
ROW(INDIRECT("1:" & LEN(A1)))
)
) - 1
)
简要解释如下:
LEN(A1)
:计算字符串A1
的长度。ROW(INDIRECT("1:" & LEN(A1)))
:生成一个从 1 到字符串长度的数字序列,表示字符串中每个字符的位置。MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1)
:使用MID
函数结合数字序列,按顺序逐个提取字符串中的单字符。UNICODE(MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1)) < 128
:将每个字符转换为 Unicode 编码,并判断其是否小于 128。如果 Unicode 编码小于 128 的字符是 ASCII 字符,而大于等于 128 的字符则是非 ASCII 字符,也可以粗略理解为中文字符。IF(UNICODE(MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1)) < 128, ROW(INDIRECT("1:" & LEN(A1))))
:根据字符是否为中文字符,返回对应字符的位置。MIN(IF(UNICODE(MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1)) < 128, ROW(INDIRECT("1:" & LEN(A1)))))
:找到第一个非中文字符的位置。MIN(...) - 1
:将中文字符的位置减去 1,得到连续中文字符的位置。LEFT(A1, ...)
:使用LEFT
函数提取字符串中从起始位置开始的连续字符,即最左侧的连续中文字符。
扩展:如果要提取最右边的连续中文,可以改成:
= RIGHT(A1,
LEN(A1) - MAX(
IF(
UNICODE(MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1)) < 128,
ROW(INDIRECT("1:" & LEN(A1)))
)
)
)
04 提取英文或数字
A1 单元格 = "数据化管理 sjhgl12345"
当字符串格式是“连续中文 + 连续(英文/数字)”,可以用下面的公式,提取右侧连续的(英文/数字):
= RIGHT(A1, 2 * LEN(A1) - LENB(A1))
计算结果:"sjhgl12345
"
同理如果字符串的格式是 “连续(英文/数字) + 连续中文”,可以用下面的公式,提取左侧连续的(英文/数字):
= LEFT(A1, 2 * LEN(A1) - LENB(A1))
计算结果:"sjhgl12345
"
05 统计指定字符的个数
A1 单元格 = "数-据-化-管-理"
= LEN(A1) - LEN(SUBSTITUTE(A1, "-", ""))
求 "-" 在 "数-据-化-管-理" 中的出现次数,计算结果:4
06 指定字符的最后出现位置
= LOOKUP(1, 0 / (MID(A1, ROW(1:20), 1) = "-"), ROW(1:20))
求最后一个 "-" 在 "数-据-化-管-理" 中的位置,计算结果:8
其中20
这个应该要能覆盖字符串的最大长度,或者改成ROW(INDIRECT("1:" & LEN(A1)))
以便自适应字符串的长度。这里用到了数组公式,简要解释如下:
MID(A1, ROW(1:20), 1)
:这部分公式使用MID
函数和ROW
函数,将字符串按字符拆分为一个个单独的字符。ROW(1:20)
返回一个数组,包含从 1 到 20 的数字序列,表示字符串的每个字符位置。(MID(A1, ROW(1:20), 1) = "-")
:这部分公式将每个字符与连字符 "-" 进行比较,返回一个布尔数组,其中TRUE
表示对应位置上的字符是 "-",FALSE
表示不是 "-"0 / (MID(A1, ROW(1:20), 1) = "-")
:这部分公式将布尔数组转换为数值数组,将TRUE
转换为 1,FALSE
转换为 0。由于除以 0 会得到错误,这里使用 0 除以布尔数组,将TRUE
转换为数值 1,FALSE
转换为数值 0。LOOKUP(1, 0 / (MID(A1, ROW(1:20), 1) = "-"), ROW(1:20))
:这部分公式使用LOOKUP
函数,在数值数组中查找最后一个 1 的位置。由于LOOKUP
函数要求数据必须是按递增顺序排列的,而 "-" 在字符串中是从左到右逐渐增加的,所以在这个数值数组中,最后一个 1 对应的位置就是最后一个 "-" 的位置。
扩展
以上用 Excel 函数去处理文本,主要是处理一些简单的情况。但是如果你要面对一些更复杂的情况,例如:
从 " 数 1 据 2 化 3 管 4 理 5
" 提取全部中文或全部数字从" 数-据-化-管-理
"提取第 2 个"-"到第 4 个"-"之间的文本
……
这些情况就建议考虑用 VBA 去处理了,使用编程中的循环 + 判断 + 字符串处理方法,结合上面公式处理问题的思路,无论多复杂的文本处理都可以搞定。
另外在文本处理还有一个神器工具-正则表达式,能让你去实现查找匹配、灵活替换文本的功能,几乎能解决所有的文本处理问题!在 VBA 中也是可以使用正则表达式的,让你整理文本数据的效率飙升!