Excel拆分单元格技巧：[3]分离数字和英文

来源：互联网 2023-02-20 19:33:26 357

前面我们分享了如何在Excel中，分离单元格中的中英文，以及分隔中文与数字。用到的公式比较简单。

本篇继续向前一步，通过Excel数组公式，重点讲解如何将单元格中的阿拉伯数字与英文字母或单词自动分离开来。

工具/原料

Excel

单元格中数字连续出现如何分离？

如果数字数目固定，显然我们可以使用简单的LEFT、RIGHT、MID等函数来自动提取。

这里就不赘述了，大家可翻看之前的两篇介绍。

我们重点来研究，当Excel单元格中，既包含数字，又包含英文字母，而且数字个数还不固定（比较随机，没有规律）的情况下，如何处理。

先看这个案例。

Excel拆分单元格技巧：[3]分离数字和英文

我们在单元格B1输入数组公式：

=LEFT(A1,COUNT(VALUE(MID(A1,ROW($1:$10),1))))

注意：

1、输入之后，不要立即按下回车，而是要同时按下快捷键Ctrl Shift Enter

这样Excel才能识别出这是数组公式。

2、如果单元格中的数字个数超过10，把公式中的10改为实际数字个数（或者更大的数）

Excel拆分单元格技巧：[3]分离数字和英文

2相关内容未经许可获取自百度经验

在单元格C1输入数组公式：

=RIGHT(A1,LEN(A1)-COUNT(VALUE(MID(A1,ROW($1:$10),1))))

同样注意，公式编辑玩最后同时按下快捷键Ctrl Shift Enter

Excel拆分单元格技巧：[3]分离数字和英文

然后，将这两个单元格的公式向下拉自动填充即可。

Excel拆分单元格技巧：[3]分离数字和英文

单元格中数字不连续出现如何分离？

接下来，我们看看更加复杂的情况。单元格中的数字间断出现，如何统一提取出来呢？

由于Excel函数公式不支持正则表达式，通配符支持也有限。

如果希望把单元格中的英文字母全部去除掉，可以反复使用SUBSTITUTE函数，把26个字母全部替换掉。

例如，在单元格B1输入公式（这个公式有点长，但是很简单）

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(A1),"a",""),"b",""),"c",""),"d",""),"e",""),"f",""),"g",""),"h",""),"i",""),"j",""),"k",""),"l",""),"m",""),"n",""),"o",""),"p",""),"q",""),"r",""),"s",""),"t",""),"u",""),"v",""),"w",""),"x",""),"y",""),"z","")

即可从混乱的字符串中得到纯数字。

如果你发现可以使用数组公式简化一下，欢迎留下宝贵的评论。

Excel拆分单元格技巧：[3]分离数字和英文

类似地，我们在单元格C1输入公式

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""),0,"")

即可得到纯英文字母了。（这个公式要短一点）

Excel拆分单元格技巧：[3]分离数字和英文