如下图所示,A 列是一些混合内容,同一个单元格内包含有科目代码和各级科目名称,不同项目之间使用“/”间隔,需要将这些科目分别拆分到右侧的各列里。 在B2 单元格输入以下公式,将公式向右向下复制。
这是一个典型的字符拆分模式化用法,接下来咱们说说这个公式的意思。 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 个空格,使其变成以下样式的新字符串: '119301 往来中转 通赔' REPT(' ',99)的作用,就是增加原有字符串中各个科目之间的间隔宽度,以便于 MID 函数分段截取出带空格的字符。 接下来使用 MID 函数,从 SUBSTITUTE 函数返回的字符串中提取字符,提取的起始位置是“COLUMN(A1)*99-98”得到的序号 1,提取长度为 99 个字符,结果为带有多余空格的内容: '119301 ' 最后使用 TRIM 函数清除字符串中的多余空格,得到科目代码“119301”。 当公式向右复制时,MID 函数分别从 SUBSTITUTE 函数返回字符串中的第 1 位、第100 位、第 199 位……依次提取出 99 个字符,并使用 TRIM 函数清除多余空格,最终得到不同的科目代码和科目名称了。 如果你使用的是Office 365,并且加入了预览体验计划,那就简单了: 在B2单元格输入这个公式,下拉就行了: =TEXTSPLIT(A2,'/') 怎么样,你学会了吗? 图文制作:祝洪忠 |
|
来自: 伊甸园hvx8yzgv > 《微信文章》