欢迎访问设·集合!

设·集合

您现在的位置是:首页 > 办公软件 > Excel

字符拆分的模式化公式,你最喜欢哪一种?

设·集合小编 发布时间:2023-06-01 14:01:34 451次最后更新:2024-03-08 12:49:22

如下图所示,A 列是一些混合内容,同一个单元格内包含有多级科目名称,不同科目之间使用“/”间隔,需要将这些科目分别拆分到右侧的各列里。

方法一:

B2 单元格输入以下公式,将公式向右向下复制。

=TRIM(MID(SUBSTITUTE($A2,"/",REPT(" ",99)),COLUMN(A1)*99-98,99))

这是一个典型的字符拆分模式化用法,接下来咱们说说这个公式的意思。

REPT函数的作用是根据指定的次数重复显示字符。

TRIM函数的作用是清除字符中的多余空格。

COLUMN 函数的作用是返回参数的列号,如果省略参数,则返回公式所在单元格的列号。

本例中的“COLUMN(A1)*99-98”部分,先使用 COLUMN 函数返回 A1 单元格的列号1,再用 COLUMN 函数的结果乘以 99 减 98,即 1*99-98,结果仍然是 1。

当公式向右复制时,COLUMN 函数会依次得到 B1、C1……的列号,再将这些列号乘以 99 减 98,即相当于 2*99-98、3*99-98……,最终得到按 99 递增的序号 1、100、199……,以此作为 MID函数的第二参数。

SUBSTITUTE 函数的作用是将字符串中的指定字符替换为新的字符,函数用法为:

SUBSTITUTE(需要处理的文本,要替换掉哪些,要替换成啥,[要替换第几个])

公式中的“SUBSTITUTE($A2,”/”,REPT(” “,99))”部分,先使用“REPT(” “,99)”将空格重复 99 次,最终得到 99 个空格。

再使用 SUBSTITUTE 函数将 A2 单元格中的每一个分隔符“/”都替换为 99 个空格,使其变成以下样式的新字符串:

“管理费用 税费 水利建设资金”

REPT(” “,99)的作用,就是增加原有字符串中各个科目之间的间隔宽度,以便于 MID 函数分段截取出带空格的字符。

接下来使用 MID 函数,从 SUBSTITUTE 函数返回的字符串中提取字符,提取的起始位置是“COLUMN(A1)*99-98”得到的序号 1,提取长度为 99 个字符,结果为带有多余空格的内容:

“管理费用 ”

最后使用 TRIM 函数清除字符串中的多余空格,得到一级科目名称“管理费用”。

当公式向右复制时,MID 函数分别从 SUBSTITUTE 函数返回字符串中的第 1 位、第100 位、第 199 位……依次提取出 99 个字符,并使用 TRIM 函数清除多余空格,最终得到不同级别的科目名称了。

 

方法二:

如果你是Excel 365的订阅用户,方法一可以直接忽略。

B2单元格输入以下公式,向下复制到B10即可。

=TEXTSPLIT(A2,"/")

TEXTSPLIT函数,是专门按指定间隔符号来拆分字符的。第一个参数是要拆分的内容,第二参数是指定的间隔符号。

Excel 365中有溢出功能,在B2单元格输入公式后,结果会自动溢出到右侧的相邻区域,就这么简单。

广告位

热心评论

评论列表