Excel数据不规范处理

今天看到这么一个案例,需要提取,或者说需要根据姓名查询!

问题虽然看上去有点麻烦,但是好在有规律,那么也就谢天谢地了!

Excel中没有文本分隔函数,所以处理类似的问题有其他的SUBSTITE+REPT套路!

我们先使用一些老套路处理!当然看到这里,已经有很多人蠢蠢欲动!这个我会,基础操作就可以!好吧,那我先基础操作吧!

思路1 | 基础操作

操作:

1、数据-分列【ALt+D+E】,使用的快捷键,分隔符选择 (|),确定完成

2、数值复制,选择性粘贴-转置

3、复制数值左边的符号(〖),再次分列

4、替换(〗)为空【Ctrl+H】

小结:基础操作永远是亲民的,但是略显繁琐,胜在简单,但是不适合大量处理!

下面我们来说说,传统处理这类问题的,函数套路!

思路2 | 函数套路法

▼我是一条普通的公式

=TRIM(MID(SUBSTITUTE(TRIM(MID(SUBSTITUTE(LEFT($A$1,LEN($A$1)-1),"〗|",REPT(" ",99)),ROW(A1)*99-98,99)),"〖",REPT(" ",99)),COLUMN(A1)*99-98,99))

简单分析:SUBSTITUTE+REPT已经是处理这类问题的老套路!把特定的符号,替换成足够多的空格,然后再进行截取,这样就可以按照统一维度截取,对于多出的空格使用Trim去掉即可!只是在案例中有多个符号,所以我们需要替换两次,一次“|”整体分离,一次姓名、分数分离!

我们来看一下中间的核心过程,大家即可明白!

把"〗|"替换成99个空格,自动换行显示如下:

这样函数是最传统的套路,在2013版本之后,出现了FILTERXML函数,这个函数可以借助网页的xpath进行提取,对于这类问题,有了更多的更有意思的解法!

思路3 | 新的套路 - FILTERXML

我们使用FILTERXML可以轻松提取出全部内容,默认一列显示

▼我是一条普通的公式

=FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(LEFT($A$1,LEN($A$1)-1),"〗|","</b><b>"),"〖","</b><b>")&"</b></a>","//b")

我们可以通过index函数轻松转成两列显示

先构建一样,结果需要的格式,然后放到INDEX第二参数即可!

=2*ROW(A1)-1+{0,1}

序列的构建是数组的基础功,大家后期慢慢总结,即可轻松掌握!

▼我是条区域数组公式(两列)

=INDEX(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(LEFT($A$1,LEN($A$1)-1),"〗|","</b><b>"),"〖","</b><b>")&"</b></a>","//b"),2*ROW(A1)-1+{0,1})

关于FILTERXML这个函数,小编已经写过专题文章,不了解基础的同学,

可以扩展阅读:FILTERXML基础用法

以上的函数函数,都是因为Excel中没有对应的文本处理方式,所以特别麻烦,截止到目前微软也就只是把文本合并的坑补上了TEXTJOIN函数,但是分割还是没有音讯!

在这种情况下,人家不给,我自己造吧!

思路4 | 自定义函数 - SplitText

支持多分隔符分割,具体介绍可以看之前的SplitText

专题讲解:SplitText支持多分隔符,正反向提取

这里如果有稍微复杂点的,其实也就是第二参数提取的序列构建:

=ROW($A1)*3-2+COLUMN(A1)-1

1、2   4、5 中间间隔一个,是因为 | 分隔会多出一组空格!

(0)

相关推荐