用excel公式提取单元格中关键数据,可以用下面的方法完成。下面是实际操作。供参考。
需要用到高版本OFFICE365中的TEXTSPLIT函数。
公式如下:=MID(SORT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTSPLIT(A21,";",,1),"一","1"),"二","2"),"三","3"),,1,1),5,FIND(",",SORT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTSPLIT(A21,";",,1),"一","1"),"二","2"),"三","3"),,1,1))-5)。
公式很长,主要处理以下几个问题:分别介绍一下。
先用TEXTSPLIT处理字符串的分列问题,以";"作为分列标记。如果是低版本,改用数据分列来解决。公式也需要做调整。如果字符串中存在中英分号混合,先用替换法统一替换为一种。方便后面处理。公式为TEXTSPLIT(A21,";",,1)。text是要分列的字符串所在的单元格。Col_delimiter是分列的标记字符串,注意,只用一个";"。不能多个("a/(;.")。Row_delimiter是分行的标记,本例不分行,只分列。
得到结果是:
用SORT+SUBSTITUTE处理数据分列后的排序。分二步完成,先把“一”,“二”,“三”,替换为“1”,“2”,“3”,再用SORT进行按列、升序排列。其中SORT_ORDER参数是,1升序,-1降序。By_col参数是1按列排序,0按行排序。
得到结果是:
最后用MID函数截取我们需要的数据部分。SORT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTSPLIT(A21,";",,1),"一","1"),"二","2"),"三","3"),,1,1)这部分虽然很长,我们把它看作一个分列、替换、排序后得到的字符串,就行了。5,是从第5个字符开始MID,后面MID多少个是这样算出来的。用FIND函数找到","(英文逗号),返回","的位置数,每个位置数不一样,减去5(“一/秒:”这四个字符+1),即相当于MID(A1,5,FIND返回的位置数-5),便截取到我们需要的数据了。
这是用手工分列,再用公式过滤后得到的结果。
这是全部用公式处理得到的结果。