Excel根据指定字符函数方法分割字符串

有时需要用excel处理一些固定格式的字符串,如系统导出的串码,Key值等,例如“Adidas-baidu-PC-V1-A1”这种格式的。用excel的分列功能当然可以做,但操作比较麻烦,而且改动了数据源,其实可以用函数方法实现:
用到的函数:

find( ) 文本查找函数
语法:FIND(find_text,within_text,start_num)
– find_text 必需。 要查找的文本。
– within_text 必需。 包含要查找文本的文本。
– start_num 可选。 指定开始进行查找的字符。 不填则默认为1.

SUBSTITUTE( ) 字符替换函数
语法:SUBSTITUTE(text,old_text,new_text,[instance_num])
– text 必需。 需要替换其中字符的文本,或对含有文本(需要替换其中字符)的单元格的引用。
– old_text 必需。 需要替换的文本。
– new_text 必需。 用于替换 old_text 的文本。
– Instance_num 可选。 指定替换第几个出现的old_text。 如果未填写,则text中出现的所有 old_text 都会更改为 new_text。

方法1:find( )和SUBSTITUTE( )组合
思路:
find( )函数可返回查找到的第一个字符的位置,但后面第N个的则需要复杂的公式嵌套。此时可用SUBSTITUTE( )用另一个字符替换第N个用于分隔的字符,再查找该替换字符,即可返回第N个分隔字符的位置。最后用 mid( )或left( )、right( )函数分隔

示例:
如A10 为需拆解的字符串,取以”-“分隔的的各个字段
第1个:=LEFT($A10,FIND(“$”,SUBSTITUTE($A10,”-“,”$”,1))-1)
第2个:=MID($A10,FIND(“$”,SUBSTITUTE($A10,”-“,”$”,1))+1,FIND(“$”,SUBSTITUTE($A10,”-“,”$”,2))-FIND(“$”,SUBSTITUTE($A10,”-“,”$”,1))-1)
……
最后1个:=RIGHT($A10,LEN(A10)-FIND(“$”,SUBSTITUTE($A10,”-“,”$”,4)))

1

2

3

以上见文末链接附件工作簿中的《示例1》

方法2:find( )嵌套
思路:
find( )函数只可以返回查找到的第一个字符位置,但该函数最后一个可缺省参数可指定开始查询位置,多个find( )嵌套,每个分割取上个分割位置+1为开始查询位置即可。

示例:
因嵌套较复杂,这里使用辅助列分解处理
如A3为需拆解的字符串,取以”-“分隔的的各个字段
第1个”-“的位置:G3=FIND(“-“,$A3,1)
第2个”-“的位置:H3=FIND(“-“,$A3,G3+1)
……以此类推
最后用mid( )或left( )、right( )引用上述位置分割字段。

4 5

详细见文末链接附件工作簿中的《示例2》

下载示例附件 https://qianrong.me/wp-content/uploads/2016/05/example_20160529.xlsx

尊重作者劳动,转载请注明出处:札记-Qianrong's Blog » Excel根据指定字符函数方法分割字符串

赞 (4)

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址