1

职场实例

小伙伴们大家好，今天我们来解决一个公众号后台粉丝咨询的一个Excel问题：如何从一列数据中批量提取每个单元格中最后一个分隔符前面的数据（也可以理解为删除最后一个分隔符及后面的数据）。这个问题看似很难，其实也有简单的突破口。

如下图所示：

A列为一列数据，我们发现每个单元格中的数据都是由多个相同的分隔符号“-”分成了若干部分（即分隔符号的数量不一定），我们想要提取最后一个分隔符号前面的内容显示在B列。

2

解题思路

这个问题的重点，也就是突破口在于如何确定每个单元格中最后一个分隔符所在的位置（即距离左侧第一个字符的距离）。那么下面我们就来利用几个基础类函数快速的解决此问题。

我们在B2单元格输入下面的函数：

=ROW($1:$99)

ROW函数可以以内存数组的展现形式返回参数“$1:$99”，即1~99行每行的行号。最大行号的确定我们要取一个相对大的值，以确保大于等于单元格的最大字符长度，保证最终统计的准确性。

公式编辑状态下选中公式部分按下F9键查看公式返回结果，以数组状态存储的1~99行的每行的行号：

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63;64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;84;85;86;87;88;89;90;91;92;93;94;95;96;97;98;99}

我们继续用ROW($1:$99)函数部分作为FIND函数的第3参数，完善为下面的公式：

=FIND("-",A2,ROW($1:$99))

FIND函数表示返回一个字符串在另一个字符串中出现的起始位置（区分大小写）。

函数结构：

=FIND(要查找的字符,被查找的字符,[从第几个字符开始查找])

如果第三参数忽略，则默认为1，即从第一个字符开始查找。

即用FIND函数在A2单元格中查找分隔符“-”，从第N个字符开始查找（这里用ROW($1:$99)的数组返回值表示）出现的起始位置。

公式编辑状态下选中公式部分按下F9键查看公式返回结果，以数组状态存储的返回结果：

{4;4;4;4;8;8;8;8;10;10;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

这里是考验我们对FIND函数第3参数基础知识的理解程度的时候了：从第N个字符开始查找，即分隔符“-”在第N个字符处及后面开始的起始位置。

以A2单元格数据“639-289-1-7”为例：

从首个字符开始查找，查找第1个字符后的首个“-”的起始位置为4，依次类推，直到第4个字符后首个“-”的起始位置仍为4；

从首个字符开始查找，查找第5个字符后的首个“-”的起始位置为8，依次类推，直到第8个字符后首个“-”的起始位置仍为8；

从首个字符开始查找，查找第9个字符后的首个“-”的起始位置为10，依次类推，直到第10个字符后首个“-”的起始位置仍为10；

从首个字符开始查找，查找第11个字符后的首个“-”的起始位置，但是第11个字符及其后就没有符号“-”了，所以一直会返回错误值#VALUE!。

我们发现了一个有趣的规律，数组内元素所有数字的个数即为“最后一个分隔符所在的位置”，所以我们用COUNT函数统计数字的个数：

=COUNT(FIND("-",A2,ROW($1:$99)))

而最后一个分隔符前面的数据的长度，要去掉最后那个分隔符的1个单位长度后，才准确，所以在公式基础上减去1即可：

=COUNT(FIND("-",A2,ROW($1:$99)))-1

最后用LEFT函数在A2单元格从左开始，提取指定长度的字符即可：

=LEFT(A2,COUNT(FIND("-",A2,ROW($1:$99)))-1)

我们就实现了批量提取每个单元格中最后一个分隔符前面的数据（也可以理解为删除最后一个分隔符及后面的数据）的效果。