Excel常用的字符串公式

  • 发布时间:2017年6月5日 16:13
  • 作者:杨仕航
  • 分类标签: Excel
  • 阅读(28232)
  • 评论(0)

在Excel中,字符串处理是经常碰到的事情。例如根据身份证号获取出生年月、性别等信息。微软已经为我们提供了不少相关的字符串处理公式。我划分几个部分给大家讲解。


1、字符串基础

关键字:双引号、&、len、lenb

在公式中,Excel使用两个双引号表示字符串。例如,= "123ab"。其中123ab是表示字符串。

如果有两个字符串(或者两个单元格内容)需要组合成新的字符串内容,可以使用&符号合并。例如:

= "123" & "ab"


将得到123ab。你可以尝试合并两个单元格的内容。

另外,有些字符串处理逻辑需要知道字符串的长度。这个可以使用len函数,例如:

= len("123ab")


结果得到5,共有5个字符。待会在截取字符串可以进一步结合其他函数使用,例如截取到倒数第2个字符等等。

上面关键字还写着一个lenb。lenb也是获取字符串长度的,不过lenb比len多了一个字母,获取到的长度可能会比len长。(我这么说是为了避免大家学习的时候混淆这两个函数。)如下两个公式:

= len("你好")
= lenb("你好")


分别得到2和4。len计算的是字符数,有几个字符就得到几。而lenb是计算字节数,1个汉字占2个字节,所以得到4。而数字和字母只占1个字节,可以利用该特性提取中文内容。这个有兴趣可以自行了解一下。


2、截取字符串

关键字:left、right、mid

字如其意,截取字符串的函数有3个:left、right、mid。分别是从左边截取、从右边截取和从中间截取。

举个栗子,有这么一个随便编造的身份证号:445366199210125678。若有雷同,纯属有缘。(在单元格输入超过15位的数字,需要加个单引号标记为文本)

在18位的前6位是籍贯,第7位到14位的8位数字是出生年月。最后4位是顺序码,里面还包含性别和校验码。

我们可以使用left函数获取前6位数字:

20170605/20170605151716722.png


left函数很简单,两个参数:第1个参数是内容;第2个参数是从左边数起要获取(截取)多少个字符。

right函数也一样,两个参数:第1个参数是内容;第2个参数是从右边数起要获取(截取)多少个字符。例如获取上面身份证号后4位:

20170605/20170605152109916.png


而mid函数要多一个参数,因为我们要指明从哪个字符开始截取,截取都多个字符。mid公式格式为

= mid(内容,从第几个字符开始,获取多少个字符)

那么,获取上面那个随意的身份证号的出身年月日公式如下:

20170605/20170605152413095.png


当然,19921012很紧凑,可以使用text函数格式化一下。这个下面提到text再讲,公式先学会使用再拓展。

我们回头看看上面说的截取到倒数第2个字符。从左边截取的left函数需要知道到底截取多少个字符,倒数第2个字符的长度比字符串总长度少1个。结合len函数可以实现该需求:

= len(A1, len(A1)-1)


3、内容查找

关键字:find

内容查找也是经常碰到的问题。有时候只是查找判断是否包含某些内容;有时候还要判断有写内容再提取。各种各样的需求都用,经常需要结合各种字符串函数进行处理。

find格式:=find(查找的内容,原文,[从哪个位置开始查找])

最后一个参数可以省略不写,默认从第1个字符开始查找。例如,判断A列是否包含字母a:

20170605/20170605154049382.png


大家可以看到若查找得到内容,则返回一个数字。该数字表明查找到的位置。若查找不到内容,则返回一个错误。这种结果不是很友好。优化一下:

20170605/20170605154402240.png


用iserr函数判断结果是否为错误。若为错误,返回True;不是错误,返回False。我们需要找到内容的话,返回True;找不到,则返回False。这个和我们预想结果相反,所以加个not函数处理。


再看看另外一个需求,例如获取A列中括号的内容:

20170605/20170605155129601.png


中括号的位置和中括号内容的长度未知。我们无法直接使用mid函数获取内容,需要先知道两个中括号的位置,才能使用mid截取。上面提到find的返回结果为查找字符串的位置。分别找到"["和"]"的位置:

B列公式:=FIND("[",A1)
C列公式:=FIND("]",A1)


20170605/20170605155454291.png


再根据这两个位置获取内容:

20170605/20170605155552137.png


4、替换

关键字:substitute、replace

替换虽然有两个函数,但replace很少用。这里就不讲了,自行了解一下。substitute经常被使用,曝光率很高。该函数格式如下:

=substitute(原文,要被替换的内容,新内容,[替换第几个])


第4个参数是可选。若不填写,表示全部替换。例如,"ABC"替换成"DDB":

20170605/20170605160104689.png


这是最基本的玩法。还可以拓展一些玩法,例如判断某些内容出现几次。基本逻辑是把内容替换成空字符。这样可以比较新旧内容的长度,从而知道替换了几处。例如判断A列有多少个短横线:

20170605/20170605160718309.png


这个只是一个字符的情况。若多个字符,需要除以字符长度。例如,判断有多少组“--”:

20170605/20170605160911303.png


还可以利用最后一个参数,实现其他各种需求。这些可自行研究。


5、其他

关键字:rept、text

rept函数是重复输出字符。很简单=rept("a", 5),可以得到5个a。

text函数是格式化函数。效果和单元格格式的自定义格式一样,可以格式化数字内容。

该函数格式很简单:=text(待格式化的内容,格式化文本)。但这个函数格式化文本的知识较多。这里不方便扩展。大家可自行了解,或者有空的时候,我再写一篇有关text函数的博客。

上一篇:解决python安装pycurl的问题

下一篇:我的网站搭建(第55天) 站内消息通知

相关专题: Excel公式学习   

评论列表

智慧如你,不想发表一下意见吗?

新的评论

清空