当前位置:首页 > office技巧笔记 > 正文

[EXCEL技巧]如何查询到一个特定的字符,出现在最后位置

文章目录
  1. SUBSTITUTE函数
  2. 语法
  3. 示例
  4. Excel应用
  5. 语法
  6. 参数
  7. 实例
  8. IP地址的实例

日常的工作中,我们有时会遇到,要查询一个字符在,一个字符串中出现的最后一个位置,比如说IP地址,我们要知道,IP地址用" . "分成四段,我们想字符截取前三段的IP范围,也就是说要把最后一个 “ .”以及之后的字符删除掉,这就遇到,我们得知道最后一个“ . ”的位置,我们在excel里,平时用到一些什么查询字符的函数,VLOOKUP,FIND,SUBSTRING ,但是这些函数一般都只能查询到字符第一次出现的位置,那有什么方法可以做到呢,这里就要讲到字符替换的一个函数SUBSTITUTE

借用百度百科的内容,说明下这个函数

SUBSTITUTE函数

在文本字符串中用 new_text 替代 old_text。如果需要在某一文本字符串中替换指定的文本,请使用函数 SUBSTITUTE;如果需要在某一文本字符串中替换指定位置处的任意文本,请使用函数 REPLACE。

语法

SUBSTITUTE(text,old_text,new_text,[instance_num])
Text 为需要替换其中字符的文本,或对含有文本的单元格的引用。
Old_text 为需要替换的旧文本。
New_text 用于替换 old_text 的文本。
Instance_num 为一数值,用来指定以 new_text 替换第几次出现的 old_text。如果指定了 instance_num,则只有满足要求的 old_text 被替换;如果缺省则将用 new_text 替换 TEXT 中出现的所有 old_text。

示例

如果您将示例复制到空白工作表中,可能会更易于理解该示例。
A
1
数据
2
销售数据
3
2008年第一季度
4
2011年第一季度
公式
说明(结果)
=SUBSTITUTE(A2,"销售","成本")
“成本”替代“销售”(成本数据)
=SUBSTITUTE(A3,"一","二",1)
用“二”代替示例中第一次出现的“一”(2008年第二季度)
=SUBSTITUTE(A4,"1","2",2)
用“2”代替示例中第二次出现的“1”(2012年第一季度)

Excel应用

SUBSTITUTE是Excel的单元格公式函数,不是VBA的内部函数,这两种概念要分清楚哦。虽然有好多函数的名称一样,用法也大同小异,但本质上是不同的哦!下面是SUBSTITUTE函数在Excel中的用法:
如果需要在一个文字串中替换指定的文本,可以使用函数SUBSTITUTE.

语法

SUBSTITUTE(text,old_text,new_text,[instance_num])

参数

Text是需要替换其中字符的文本,或是含有文本的单元格引用;
Old_text是需要替换的旧文本;
New_text用于替换old_text 的文本;
Instance_num 为一数值,用来指定以new_text 替换第几次出现的old_text;如果指定了instance_num,则只有满足要求的old_text 被替换;如果缺省则将用 new_text 替换 TEXT 中出现的所有 old_text。

实例

如果A1=学习的革命、A2=电脑
则公式=SUBSTITUTE(A1,"的革命",A2,1)
返回“学习电脑”
要查看Excel中所有可用函数,可以点Excel编辑栏左边那个“fx”图标,里面包含了所有函数的说明。
要查看VBA中所有可用函数,可以在VB窗口点帮助菜单,里面的语言参考包含了VBA的所有函数、语句、属性、方法、对象等等的详细说明。

IP地址的实例

 举个例子,IP地址的字符串
ip(要截取的部分)ip(原IP)
1.127.1271.127.127.254

如上所示 IP地址  1.127.127.254 ,我们想得到 1.127.127 ,思路是这样的:将最后一次出现的空格替换为其他某一特殊字符,然后再用 find 查找这一特殊字符的位置即可。具体步子如下:
1)空格出现的次数:len(B2)-len(substitute(B2," ",""))
2)将最后一个空格的替换为@:SUBSTITUTE(B2,".","@",len(B2)-len(substitute(B2," ","")))
3)查找@所在位置:FIND("@",SUBSTITUTE(B2,".","@",len(B2)-len(substitute(B2," ",""))),1)

4)MID截出字符:=MID(B2,1,FIND("@",SUBSTITUTE(B2,".","@",len(B2)-len(substitute(B2," ",""))),1)-1)

这样子大功告成!!!!

标签:
上一篇: 下一篇:

1 条评论

评论加载中...
  1. 沙发
    来自天朝的朋友 谷歌浏览器 Windows 10
    quanshui   
    为啥是空的
    2017-12-13 12:28 评论

发表评论

不理你。 不要啊! 吃饭。 吃惊。 吃西瓜。 飞吻! 恭喜! Hi 纠结! 膜拜! OK 抛媚眼。 泡泡糖。 抛钱。 忍! 生闷气! 调皮。 偷看。 委屈。 献花。 疑问? 抓狂!

小提示:Ctrl+Enter快速提交助您一臂之力~
加载中……