在做数据分析的过程中,有时我们要整理文本数据,本文总结了十大经典的文本处理套路,供大家参考使用。本文的呈现虽然是以 Excel 公式为载体,但重点是掌握其处理思路,即使你是使用 PQ、VBA 编程的时候,思路也是同样适用的。暂时用不上的话,也建议先 最基本的文本函数以下这些都是最基本的文本字符处理函数,当然都是必须要掌握的。 LEFT= LEFT('数据化管理', 2) 计算结果是:' RIGHT
计算结果是:' MID= MID('数据化管理', 3, 1) 计算结果是:' LEN
计算结果是: SEARCH= SEARCH('化', '数据化管理') 计算结果是: SUBSTITUTE
计算结果是:' 以上 6 个就是基础的文本处理函数,语法也很清楚,大家一个个看的话,肯定都能明白的。但 Excel 公式的运用,重难点是在于函数的嵌套。因为函数的嵌套其实就是算法,写算法就要考你的逻辑理解能力了,当然我们借鉴别人的算法也是很常见的,但前提是你必须要理解,这样才能玩得转! 以下就是结合具体文本处理场景的一些经典嵌套公式 截断固定长度
A1 单元格的值是'?数据化管理' = RIGHT(A1, LEN(A1) - 1) 计算结果是:'
A1 单元格的值是'数据化管理?'
计算结果是:' 基于分隔符提取A1 单元格的值是'数据化-管理'
= LEFT(A1, SEARCH('-', A1) - LEN('-')) 计算结果是:'
计算结果是:' A1 单元格的值是'数据-化-管理'
= MID(A1, SEARCH('-', A1) + LEN('-'), SEARCH('-', A1, (SEARCH('-', A1) + LEN('-'))) - (SEARCH('-', A1) + LEN('-'))) 计算结果是:' 扩展:有多个分隔符,想跳过一些分隔符?用公式就要再加嵌套,用编程就要循环了! 提取中文、英文、数字A1 的值是'数据化管理 sjhgl12345'
计算结果是:'
= RIGHT(A1, 2 * LEN(A1) - LENB(A1)) 计算结果是:' 扩展:待提取的内容不是刚好在左边,也不是刚好在右边?如果是复杂的中文、英文、数字混合情况,这时不建议用公式了,可以试试快速填充功能 更多情况A1 单元格的值是'数-据-化-管-理'
计算结果是:
LOOKUP(1, 0 / (MID(A1, ROW(1:20), 1) = '-'), ROW(1:20)) 扩展用 Excel 函数去处理文本,主要是处理一些简单的情况。如果你是要面对一些更复杂的情况,例如:
…… 这些情况就建议考虑用 VBA 去处理了,使用编程中的循环 + 判断,结合上面公式处理问题的思路,无论多复杂的情况你都可以搞定。在文本处理界还有一个神器工具-正则表达式,能让你去实现查找匹配、灵活替换文本的功能,几乎能解决所有的文本处理问题!在 VBA 中也是可以使用正则表达式的。 对于我们做数据分析的朋友来说,学习 VBA 主要是想提升数据处理、数据分析的效率,还有就是与其他 Office 软件进行联动,打造一些办公自动化的场景,让工作效率更高! 我有推出一个从入门到应用的 VBA 课程,有兴趣的朋友可以 |
|