Excel·VBA文本按分隔获取函数_vba对单元格输出-后面的内容

office 365新增函数《TEXTAFTER 函数》和《TEXTBEFORE 函数》可以对单元格文本，获取指定分隔符之前/之后文本

对于没有office 365又想使用这个函数，就只能自己写VBA代码自定义函数了

instance_num参数：正数为从左往右，1为最左；负数则相反，-1为最右

Function text_take(text As String, Optional delimiter As String = "，", Optional mode As String = "a", Optional instance_num As Long = 1)
    '函数定义text_get(文本，分隔符，模式之后/之前、第几个分隔符)对文本按模式获取指定分隔符之前/之后的文本，返回一个文本
    '2种模式，"a"即after获取分隔符之后、"b"即before获取分隔符之前
    Dim arr, result, i&, j&, min_a&, max_a&, start_a&, end_a&
    If instance_num = 0 Then text_take = "#instance_num必须为非0整数": Exit Function
    arr = Split(text, delimiter)  '分割函数，返回从0计数的一维数组
    min_a = LBound(arr): max_a = UBound(arr): j = 0
    If max_a = 0 Or Abs(instance_num) > max_a Then text_take = "#N/A": Exit Function
    If LCase(mode) = "a" Then
        If instance_num > 0 Then start_a = instance_num Else start_a = instance_num + max_a + 1
        ReDim result(0 To max_a - start_a)
        For i = start_a To max_a
            result(j) = arr(i): j = j + 1
        Next
        text_take = Join(result, delimiter)
    ElseIf LCase(mode) = "b" Then
        If instance_num > 0 Then end_a = instance_num - 1 Else end_a = instance_num + max_a
        ReDim result(0 To end_a - min_a)
        For i = min_a To end_a
            result(j) = arr(i): j = j + 1
        Next
        text_take = Join(result, delimiter)
    End If
End Function
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

举例