【原】Excel VBA 8.19 Excel一次只能按照一种分隔符拆分 VBA可以多种

Excel和VBA 2021-09-07

展开全文

Excel一次只能按照一种分隔符拆分 VBA可以多种

点击上方“Excel和VBA”，选择“置顶公众号”

致力于原创分享Excel的相关知识，源码，源文件打包提供

一起学习，一起进步~~

昨天我们学习了如何利用VBA来实现拆分单元格的功能，很多小伙伴也是一直在吐槽，这个功能和Excel自带的分列功能有点类似，似乎实际工作中的作用不大，好吧，既然如此，我们来升华一下，今天难度提升，昨天我们拆分的单元格只有一种分隔符，按照大家的说法，可以直接用Excel自带的分列功能实现，那么如果有很多种不同的分隔符呢？

场景说明

这是我们今天的模拟数据，可以看到，今天的数据源中有很多种不同的分隔符，也有空格，如果我们还是用Excel自带的分列功能呢？

是不是有点束手无策了，因为Excel的分列功能每次只能按照一种分隔符进行拆分，没有办法同时针对多种拆分符进行分割，所以这就是他的局限性，也许有小伙伴会说，多拆分分列几次就可以了，事实是这样，当然是你不嫌麻烦

其实我们完全可以用VBA来实现一次性拆分的

如何实现呢？

其实就是在上节的代码基础上增加一些代码，来一起看看

代码区

Sub test2()Dim rng As Range, arr, a As Range, strSet rng = Application.InputBox("请选择需要拆分的单元格区域", "单元格的处理", , , , , , 8)For Each a In rng str = a.Value With CreateObject("VBSCRIPT.REGEXP") .Pattern = "[.~!@#$%\^\+\*&\\\/\?\|:\.{}()', ;=]" .IgnoreCase = True .Global = True If .test(str) Then ss = .Replace(str, "-") arr = Split(ss, "-") ii = UBound(arr) + 1 For i = 1 To ii a.Offset(0, i) = arr(i - 1) Next i End If End WithNext aEnd Sub

来看看代码实现的效果

成功的一次性将所有的数据进行了拆分，不管是空格，还是其他的各种分隔符，都能够通过这个代码一次性拆分出来，很明显这个功能Excel的分列是不能实现的，现在大家知道我为什么要讲解如何利用VBA来实现简单的Excel的分列功能了吧，有了这个基础，我们才可以升华的

代码解析

今天的代码，其实逻辑是非常的简单的，既然没有办法一次性将多种分隔符进行拆分，那么我们换个思路

将所有的拆分符全部替换成一个统一的分隔符不就可以了吗？

然后利用上节课学习的拆分方法，针对某种拆分符进行拆分数据，答案就出来了。

按照这个思路走下去，既然要将所有的拆分符都替换成为一种统一分拆分符，要如何实现呢？

这里我选择使用正则

VBA可以使用正则！

不要吃惊，对于VBA来说，使用正则是常规操作，来看看代码

For Each a In rng str = a.Value With CreateObject("VBSCRIPT.REGEXP") .Pattern = "[.~!@#$%\^\+\*&\\\/\?\|:\.{}()', ;=]" .IgnoreCase = True .Global = True If .test(str) Then ss = .Replace(str, "-") Next i End If End WithNext a

这里我们将代码拆开来，分成两部分进行讲解

先来说说大家最期待的部分，VBA搭配正则的使用。

要想在VBA中使用正则，起手式是这样的

With CreateObject("VBSCRIPT.REGEXP") **********End Wit

至少要声明一下，告诉VBA，我现在要使用正则了。

pathern后面就是我们要替换的常用的拆分符，如果你要的拆分符在上面没有，那就可以直接在这句话里面添加

注意，一定要加在【】里面

这个正则的作用，主要是看单元格内容中是否存在【】中间的拆分符，如果存在，则将所有的拆分符统一替换成为“-”

ss = .Replace(str, "-")

具体的，我会在后面单独开一个章节来讲解正则的知识，大家这里先了解下，记住这种写法就好。

既然成功的将所有的拆分符替换成为了统一的一种拆分符

后面的操作是不是和昨天的代码一样了。