EXCEL函数篇01 按间隔符提取数据,实现分列功能 |
您所在的位置:网站首页 › 怎么把表格的数据分列开出来 › EXCEL函数篇01 按间隔符提取数据,实现分列功能 |
如下图所示,A列是数据源,每个单元格的数据以符号“,”作为间隔组合在一起,例如:“苹果,香蕉,苹果”。 一、单元格同时存放两个字符 第一字符:=LEFT(A2,FIND(",",A2)-1) 第二字符:=RIGHT(A2,FIND(",",A2)-1) LEFTFIND函数发现"/"在A2单元格中首次出现的位置,然后使用LEFT函数从左向右提取该长度的字符,即为结果。 二、单元格同时存放三个字符 第一字符:=LEFT(A2,FIND(",",A2)-1) 第二字符:=TRIM(MID(SUBSTITUTE(A3,",",REPT(" ",100)),100,100)) 第三字符:=RIGHT(A2,FIND(",",A2)-1) 和第2个公式有些相似,依然先使用SUBSTITUTE函数,将A2单元格中的","替换为100个空格,这样就将不同的值划分到了由空格间隔而成的多个小房子里,再使用MID函数从指定区段取值,最后使用TRIM函数消除空格。 SUBSTITUTE(A2,",",REPT(" ",100)),这部分公式将A2中的","替换为100个空格,然后RIGHT函数从右边提取100个字符,这100个字符必然包括了最后一个","后的数据以及大部分的空格,因此最后用TRIM函数清除空格即为结果。 三、单元格存放三个以上字符 B2输入以下公式,向右向下复制填充至B2:E5区域,即可得出结果: =TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",100)),COLUMN(A1)*100-99,100)) 该公式和第3个公式类似,只是使用COLUMN(A1)*100-99取动态区间,随着公式的向右拖动,依次提取第1~101~201个字符起的 100个字符结果,最后依然使用TRIM函数清理空格。
小贴士: 1、TRIM+MID+SUBSTITUTE是一个非常经典的字符串处理函数套路,尤其擅长与处理间隔符的问题,这是完全可以用快速填充或者分列功能,没必要非函数不可。但是函数和基础操作的区别在于,函数处理问题更加自动化、模块化;它的计算结果可以直接嵌套在其它函数中继续使用,避免手工反复操作。 2、原理补充: LEFT函数: 从一个文本字符串的第一个字符开始返回指定个数的字符。 函数结构: =LEFT(字符串,[提取几个字符]) 第二参数如果忽略,则默认为1。 RIGHT函数: 从一个文本字符串的最后一个字符开始返回指定个数的字符。 函数结构: =RIGHT(字符串,[提取几个字符]) 同样,第二参数如果忽略,则默认为1。 MID函数: 从文本字符串中指定的起始位置起,返回指定长度的字符。 函数结构: =MID(字符串,开始提取的位置,提取几个字符) =MID(A3,5,3) 注意:LEFT/RIGHT/MID均为文本处理函数,提取的结果亦为文本,若需参与计算,需转化为数值。 FIND函数: 返回一个字符串在另一个字符串中出现的起始位置(区分大小写) 函数结构: =FIND(要查找的字符,被查找的字符,[从第几个字符开始查找]) 如果第三参数忽略,则默认为1,即从第一个字符开始查找。 =FIND("-",A3,5)
|
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |