分类：处理字符串

Excel计算特定字符在单元格中的数量（查找统计指定内容）

如果只是计算单元格内的字符数，用 LEN 函数就能做到。但如果想要计算单元格内特定字符的个数，应该怎么做？

像下图这样，数据表的 A 列中含有表示 URL 的字符串。

A 列中含有 URL 数据

这时，我们应该如何计算 A 列中斜线符号（/）的数量，并让其显示在 B 列中呢？

像这样，想要计算单元格内特定的字符的数量，首先需要在单元格 B2中输入以下公式：

=LEN(A2)-LEN(SUBSTITUTE(A2,”/”,””)

将这个公式一直复制到数据最末行，就能提取每个 URL 中的斜线符号（/）的数量。

单元格 B2中输入=LEN(A2)-LEN(SUBSTITUTE(A2,”/”,” “)，一直复制粘贴到单元格 B6

在此，使用 LEN 函数与 SUBSTITUTE 函数，提取出在单元格 A2中有多少个“/”，公式如下。然后将2个数值相减即可得出想要的结果。逻辑如下：

单元格 A2的字符数减去 A2单元格字符串中除去“/”之后的文字数。

首先，用 LEN（A2）计算出单元格 A2的字符数，该数据为23。LEN(SUBSTITUTE(A2,”/”,””))的部分，是将 SUBSTITUTE 函数作为参数嵌入到 LEN 函数的。作为参数的 SUBSTITUTE 函数可以将单元格 A2中的斜线（/）替换为空白，然后再用 LEN 函数计算出单元格 A2中除去斜线后的字符数。由此可以得出此例中的单元格 A2的字符数为20。

二者相减后可得到3，就是单元格 A2中的斜线（/）的数量。

Excel连续输入26个英文字母

Excel 设有“自动填充”功能。比如在单元格 A2中输入“星期一”，向下复制粘贴，就能自动从“星期一”开始连续填入数据。

在单元格 A2中输入“星期一”，向下复制粘贴，自动连续填充数据

这种自动填充其实无法连续输入英文字母。但是，“想要从 A 开始按顺序连续输入项目名称”这种需求非常常见，解决方法有两种。

➊ 使用 CHAR 函数

比较简单的方法就是使用 CHAR 函数。它可以将参数指定的字符代码转换成字符。

比如，英文字母 A 对应的字符代码为65。也就是说，输入下列公式的单元格中会显示 A。

=CHAR(65)

将字符代码65改为66则得出 B。也就是说，每次增加1个字符代码且连续输入 CHAR 函数的话，就会在表格中连续输入英文字母。比如，想要从单元格 A2开始沿列连续输入英文字母，那么我们可以在参数中嵌入 ROW 函数，输入以下函数公式：

=CHAR(ROW()+63)

单元格 A2位于工作表的第2行，因此这一公式的 ROW 函数在 A2中会得出2。以2为调整数值加上63，就能得到 A 的字符代码65。

输入这个公式并复制粘贴到其他单元格中，就能输入连续的英文字母了。

在单元格 A2中输入=CHAR(ROW()+63)，一直复制粘贴到单元格 A27

➋ 使用 SUBSTITUTE 函数与 ADDRESS 函数

下列公式，可设置成在 Z 之后继续输入 AA、AB 的形式。

=SUBSTITUTE(ADDRESS(1,ROW()-1,4),1″”)

请大家先自行解读一下这个公式。将这个公式输入到第2行并向下复制粘贴。

“从无到有，需要自己创造。”

这是成年人在处理工作上的基本法则。而且，在想要做到使用 Excel 的基础功能无法完成的处理时，也要有这样的精神。

Excel如何删除指定文字（替换指定内容）

接下来，将单元格 B2中删除连字符（-）后的数值提取到单元格 C2中。像这样，想要删除指定文字时，可以使用 SUBSTITUTE 函数。SUBSTITUTE 意为“替换”。

=SUBSTITUTE(B2,”-“,” “)

在单元格 C2中输入=SUBSTITUTE(B2,”-“,” “)

这个函数，是在第一参数指定的字符串的范围内，是将第二参数指定的文字替换为第三参数指定的文字。在这个例子中，第三参数为” “（空白），将连字符替换为空白，就是删除连字符。

整合这些逻辑的话，就是下面的公式。先用 ASC 函数转换为半角形式的字符串，再用 SUBSTITUTE 函数将连字符替换为空白。

=SUBSTITUTE(ASC(A2),”-“,” “)

把上面的公式一直复制粘贴到数据最末行，表格中所有电话号码就会变成统一的格式。

将=SUBSTITUTE(ASC(A2),”-“,” “)一直复制粘贴到数据最后一行

Excel如何判断字符串是否相同（内容找不同并标出）

我们在手动输入数据时，很容易发生格式不统一的情况。如果想将单元格中的数据整理成统一的格式，就需要花费大量的时间。

比如，在全角格式下输入的电话号码。为了检查客户名单中是否存在重复，我们需要以电话号码为标准，使用 COUNTIF 函数判定是否存在重复。这时，我们需要将所有的电话号码整理成统一的格式。即便是分别用全角和半角格式输入的相同的电话号码，在 Excel 中也不能将其判定为相同数据。

如下例，在 A 列中输入了两个相同的电话号码，但是单元格 A2中的数据为全角格式，单元格 A3为半角格式。单元格 B2输有 EXACT 函数（下列公式），用来判定两个字符串内容是否相同。

=EXACT(A2,A3)

判断单元格 A2与单元格 A3中的字符串是否相同（单元格 B2）

EXACT 函数，指定参数的两个字符串如果相同为 TRUE，不同则返回 FALSE。因此在该例中，结果为 FALSE（不同）。

这种情况下，我们需要统一数据格式。这种操作在不少处理字符串相关的函数中发挥着作用。

Excel如何提取单元格内容中空格的前后两部分

运用连字符“&”可以合并字符串，但是要拆分字符串多少有点复杂。例如，像下面这样用半角空格隔开姓氏和名字的情况下，怎样才能把姓氏和名字分别提取到不同单元格中呢？

姓氏与名字以半角空格隔开的数据

这种情况下，如有半角空格等形式的“分隔文字”（将空格视为1个字符），其实也能做到把空格前后的数据提取到不同单元格中。我们来看一下操作顺序。

➊ 提取姓氏

首先提取姓氏数据。提取单元格中的姓氏就是说“从左开始提取多少单元格内的字符串中的字符”，这里要用到 LEFT 函数。问题在于如何提取指定的字符数。

这里，我们需要知道“分隔文字是第几个字”。例如，单元格 A2中的“吉田拳”，其分隔文字是半角空格，是第3个字符。接下来，用3减去1可以得出2，也就是说从左开始抽取2个字符即可得到姓氏。换句话说就是这样：

“分隔文字为第几个字符，用这一数字减去1所得到的数字，就是需要从字符串左边开始提取的字符数。”

公式如下：

=LEFT(A2,FIND(” “,A2)-1)

接下来，要注意如何在第二参数中使用 FIND 函数。这是用于定位指定文字在单元格内的位置的函数。

并且，这样连续输入两个引号（””）则表示“空白”，如果在双引号之间加入半角空格（” “），则表示“半角空格”。

将这一公式输入进单元格 B2，就可以在 B2中提取单元格 A2中的半角空格之前的字符，在这里就是姓氏数据。

在单元格 B2中输入=LEFT(A2,FIND(” “,A2)-1)

我们既然已经知道分隔文字的半角空格是第3个字符，那么要想提取姓氏，需要从字符串左侧开始应该提取的字符就是3减1，即两个。这样，就能够只提取出“吉田”这两个字，也就是位于字符串最左侧的两个字符。

➋ 提取名字

接下来，我们来提取名字。这次需要从右开始提取，所以要用到 RIGHT 函数。问题在于应该如何设定“从右侧开始提取的字符数”。我们可用下面的函数公式处理。

=RIGHT(A2,LEN(A2)-FIND(” “,A2))

在单元格 C2中输入=RIGHT(A2,LEN(A2)-FIND(” “,A2))

在 RIGHT 函数的第二参数中，使用 LEN 函数和 FIND 函数指定了需要提取的字符数。“用单元格 A2的字符数减去单元格 A2中半角空格所在第几个文字后得到的数字”，按照这样的方式进行计算。在这个例子中，单元格 A2的字符数是4（半角空格也算作1个字符）。

半角空格是第3个文字，所以4-3=1。在单元格 A2的右侧开始提取1个文字，即半角空格之后的字符，也就是提取出名字。

接下来，将公式复制到下面几行，就能进行同样处理了。

将公式复制到其他单元格

但是，这种处理方式，如果遇到没有空格的情况（此例中，姓氏与名字之间没有半角空格）就无法使用了。最初在输入数据时的操作会给后续的操作带来影响，因此需要谨慎考虑。原则上来说，最好的办法就是“尽量做细致划分”。后面可根据实际情况再行合并单元格或字符串。

Excel如何从住址中区分省市与下级地方行政区

那么，在前文的表格中，如何在 C 列中提取除都道府县外的市町村等级别的数据呢？

在这一点上，还是“思考方法”最为重要，并且“思考有什么更简便的方法”也很重要。

我们需要事先了解 Excel 具体有何种类型的函数。即便不清楚，也应该思考“使用什么函数可以完成这项处理”？

首先，想从住址中提取都道府县的话，使用 LEFT 函数确定“从左开始抽取多少文字”。另一方面，想提取出市町村的话，就要考虑“从右开始提取多少文字”，此时使用 RIGHT 函数。

接下来的处理需要用到能够“计算单元格内字符数”的函数。这时我们要用到 LEN 函数。LEN 就是 Length（长度）的意思。通过以下公式，得出单元格 A1中的字符数。

=LEN(A1)

了解这个函数后就会获得好的想法。

在前文的例子中，A 列中有地址数据，旁边的 B 列中只提取出都道府县的数据。在这个状态下，想要在 C 列中提取都道府县以下的行政区的数据，就需要思考在 A 列中需要从右数提取多少字符。答案如下：

“从地址栏的字符数中减去都道府县栏的字符数，从 A 列中数据的右侧开始提取。”

可以利用以下公式实现这一点。从单元格 A2内右侧开始，提取单元格 A2的字符数减去单元格 B2字符数的字符数。

=RIGHT(A2,LEN(A2)-LEN(B2))

将这个公式输入单元格 C2，一直复制到数据最后一行，就可提取出所有地址中都道府县以下的地方行政区的数据。

在单元格 C2中输入=RIGHT(A2,LEN(A2)-LEN(B2))，一直复制到单元格 C12

Excel只从住址中选出特定地名（县、市、省等）

“住址如果是以都道府县为开头的文本，现在需要把都道府县与下级地址数据分开”。

这种操作是拆分字符串的基础。从根本上来说，为避免后期进行这样的操作，应该在制作工作表时“就将都道府县放入单独的单元格中”。但是，如果在原工作表中已经是同时出现在一个单元格中的状态的话就必须要拆分单元格了。这时，我们需要掌握如何将都道府县的数据单独提取到其他单元格中。

想要解决这个问题，仅仅熟知 Excel 中的功能和函数是不够的，重点在于以独立思考出多种处理方法。

首先，我们来思考这一问题“日本的47个都道府县名是什么类型的数据呢？”大多为3个或4个文字吧。

其中，4个字的只有“和歌山县”、“神奈川县”、“鹿儿岛县”这3个县。四个字的县名，每个都搭着“县”字，剩余全部都是3个字。

明白这一点，就能按照以下逻辑，从住址单元格中提取出都道府县的数据了。

“如果住址单元格中的第4字为‘县’，只抽选左数4个字符；（第4字不是‘县’）否则，只抽选左数3个字符”。

以上逻辑若转换为 Excel 函数，就是下面的公式。

=IF(MID(A2,4,1)=”县”,LEFT(A2,4),LEFT(A2,3))

复制粘贴含有这一公式的单元格，就能做到提取所有单元格中的都道府县名。

在单元格 B2中输入=IF(MID(A2,4,1)=”县”,LEFT(A2,4),LEFT(A2,3))，一直复制到单元格 B12

“第4字符为‘县’”这一条件，就是”从地址单元格的4个字中只提取1个字符的结果即为‘县’”，可以使用 MID 函数实现这一点。根据这一逻辑的判定真伪结果不同，用 LEFT 函数改变提取的字符数，并用 IF 函数指定操作。

代表日期的8位数变为日期数据

这里，让我们来看一下如何运用这3个函数将表示日期的8位数值转换成日期数据。

我前一章中曾提到过用 Excel 处理日期数据时，需要像下面这样用“/”将年、月、日隔开。

2013/11/12

但是，有些公司也会用“20131112”这样的8位数值来表示日期。但是，这并不是常规的日期形式，只是一种数值，我们也无法运用该数值计算出天数或星期。因此，我们需要先将其转换成日期的数据形式（序列值）。

我们可以使用 DATE 函数制作序列值。比如，想要制作“2014/1/1”这个日期数据，首先按照下列方式，在第一参数中指定公历年，第二参数中指定月份，第三参数指定日期。

=DATE(2014,1,1)

那么，如何从单元格 A2的“20131112”中提取年、月、日的数值呢？请大家按照以下思路思考。

  • “年”的数值，提取单元格 A2“20131112”左数4个字符“2013”
  • “月”的数值，提取单元格 A2“20131112”第5个字开始的2个字符“11”
  • “日”的数值，提取单元格 A2“20131112”右数2个字符“12”

像这样，想要从目标单元格的数据中提取一部分文字，就要用到 LEFT 函数、MID 函数和 RIGHT 函数。

想要抽取单元格 A2左数第4个字符，需要在 B2中输入以下公式：

=LEFT(A2,4)

在单元格 B2中输入=LEFT(A2,4)

接下来导出月份数值。请按以下方式输入 MID 函数，在单元格 A2中从第5个字开始提取2个字符。

=MID(A2,5,2)

在单元格 C2中输入=MID(A2,5,2)

最后提取日期数值。为了返回单元格 A2右数2个字符，按以下公式输入 RIGHT 函数。

=RIGHT(A2,2)

在单元格 D2中输入=RIGHT(A2,2)

像这样，分别提取出年、月、日的数据后，再按照以下方式指定 DATE 函数的参数，我们就能够得到该日期的序列值。

在单元格 E2中输入=DATE(B2,C2,D2)

上述的操作步骤可通过以下公式在1个单元格中集中处理。

=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))

LEFT 函数、RIGHT 函数和 MID 函数能够从字符串的左数或右数的，以及从字符串中间开始只提取指定的字符数，是字符串处理的基础操作。灵活运用这些函数，可以自由应对不同的数据处理需求。

将单元格中的一部分字符串移至其他单元格内

Excel 的方便之处不仅限于统计数值这一项，字符串在迅速处理单元格内的内容时也发挥着强大的作用。这里，我向大家介绍一下在处理各种数据时必须掌握的字符串处理技巧。

首先，我们需要掌握如何把单元格内的字符串的一部分提取至其他单元格的函数。这里所谓的“一部分”，指的是譬如“左数几个字”“右数几个字”“中间几个字”这样的范围。其对应的函数为 LEFT 函数、RIGHT 函数和 MID 函数。

  • =LEFT(A1,3)➛抽取单元格 A1左数3个字符
  • =RIGHT(A1,4)➛抽取单元格 A1右数4个字符
  • =MID(A1,5,2)➛抽取单元格 A1第5个字开始的2个字符

LEFT 函数与 RIGHT 函数，第一参数指定单元格的左起或右起，第二参数指定只返回多少个字符。

MID 函数，第二参数指定开始提取的位置，第三参数指定抽取的字符数。