每天一篇Excel技术图文
  微信公众号：Excel星球
  
  NO.670-按指定分隔符分段取数据
  作者：看见星光
   微博：EXCELers / 知识星球：Excel
  
HI，大家好，我是星光。
  
打个响指，出一道练习题。如下图所示，C列为数据源，有些数据以"/"为分隔符串联在一起。
  

  
现在需要...▼
  
1)提取最后一个分隔符“/”后的数据，如果数据中不存在该分隔符，则返回空，例如C6单元格。
  
2)提取第2个和第3个“/”之间的数据；如果数据中只有两个“/”，则提取第2个"/"之后的所有数据；如果两个条件都不符合，则返回空，例如C3单元格。
  OK，进入答题时间……只要结果和D:E列模拟数据一致就好，不用扩展各种其它情况。
  ……
  分享几种解法，包含了函数、PowerQuery、VBA…
  
  
思路都比较常规，先按指定间隔符将字符串分段，然后取值。VBA可以使用Split函数，PowerQuery可以使用Text.BetweenDelimiters函数，工作表函数没有Split性质的函数，就比较苦笔，使用了TRIM+SUBSTITUTE+REPT的套路(详情可以参考函数系列教程字符串拆分章节)。
  
1，函数
  

   
问题1： 
   
=IF(ISERR(FIND("/",A2)),"",TRIM(RIGHT(SUBSTITUTE(A2,"/",REPT(" ",99)),99)))
   
问题2：
   
=TRIM(MID(SUBSTITUTE(A2,"/",REPT(" ",99)),200,99))
  
  
2，
  
Power Query
  
  
代码看不全可以左右拖动..▼
  
let    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],    列1 = Table.AddColumn(            源,            "最后一个/后的数据",            each Text.Middle(                [数据],                List.LastN(                    Text.PositionOf([数据]&"/","/",2),                    2                ){0}+1,                99            )        ),    列2 = Table.AddColumn(            列1,            "第2个和第3个/之间的数据",            each Text.BetweenDelimiters(                [数据],                "/",                "/",                1            )        )in    列2
  3，VBA编程
  解法1：
   SPLIT函数..▼
  代码解析见注释，
  代码看不全可以左右拖动..
  
  
Sub byVBA_Split()    Dim arr, r, i As Long, l As Long, rng As Range    Worksheets("UI").Select    Set rng = Range("c1:e" & Cells(Rows.Count, 3).End(xlUp).Row) '数据源装入数组    arr = rng.Value    For i = 2 To UBound(arr) '遍历每一行        arr(i, 2) = "": arr(i, 3) = "" '清空结果        r = Split(arr(i, 1), "/")        l = UBound(r)        If l > 1 Then '如果存在两个以上的/            arr(i, 3) = r(2): arr(i, 2) = r(l)        ElseIf UBound(r) > 0 Then            arr(i, 2) = r(l)        End If    Next    rng = arr    MsgBox "ok"End Sub
  
  解法2：
   正则表达式..▼
  
Sub byReg() 'by二肥    Dim reg As Object, arr, i As Long, rng As Range, s As String    Dim l As Long, mh As Object    Set reg = CreateObject("VBScript.Regexp")    Worksheets("UI").Select    Set rng = Range("c1:e" & Cells(Rows.Count, 3).End(xlUp).Row)    arr = rng.Value    With reg        .Pattern = "(?:/)(.*?)(?=/|$)" '正则表达式        .Global = True        For i = 2 To UBound(arr)            s = arr(i, 1)            If .test(s) Then '如果正则表达式匹配                Set mh = .Execute(s) 'matches对象                l = mh.Count                If l > 2 Then                    arr(i, 2) = mh(l - 1).submatches(0)                    arr(i, 3) = mh(1).submatches(0)                Else                    arr(i, 2) = mh(l - 1).submatches(0)                End If            End If        Next    End With    rng = arrEnd Sub
  ……
  木了，盖木欧瓦，下期再见。
  案例文件下载百度网盘..▼
  https://pan.baidu.com/s/1D96z0_yQ4goLLHgJYW9_pg 
  提取码: tqnh 
  从0到1、从入门到实战...
  图文/视频系统教程+微信群答疑...
  函数、透视表、VBA、PQ、SQL
  
  教程全覆盖，想学什么你就学什么……
  下方识别二维码加入我的知识星球▼
  
  
 
 

Java使用String.split方法处理时末尾存在重复分隔符
 
场景
 
有一个份文件数据需要读取并写表。
 某日在生产环境中遇到了数组越界ArrayIndexOutOfBoundsException异常，分析日志、代码后发现是使用List.get()方法时，传入的索引值超出了数组的长度导致。
 
正常的数据格式A：
 AAA|BBB|CCC|DDD|EEE
 当最后一个栏位没有值时（并不代表该字段不存在，只是没有传值）的数据格式B：
 AAA|BBB|CCC|DDD|
 
注意字符串末尾的差别，使用String.split("\|")去分割该字符串时，A格式处理后返回的数组长度是5：[AAA,BBB,CCC,DDD,EEE]；
 而B格式处理后的结果是**[AAA,BBB,CCC,DDD]，但是我想要的结果是[AAA,BBB,CCC,DDD,]**，最后一个栏位并不是不存在，只是为空值而已。
 
查看API文档后发现，直接使用String.split("\|")时，内部其实调用的是split("\|",0)方法。具体自行查看API文档。
 
解决办法：
 使用String.split("\|",-1)方法，传入-1的意思是尽可能多地分割字符串，包含末尾的空字符串。

参数：
 
 
  
Code ：查询的内容
  
Zone ：查询的区域
ColumnIndex ：指定返回列的序号
Splitor ：行分隔符

 '连接字符串，类似PHONETIC，但是可以添加分隔符
 Function ConnectString(Zone As range, Splitor As String)
     Dim columnCount As Long, rowsCount As Long
     Dim resultStr As String
     Dim i As Integer, j As Integer
     columnCount = Zone.Columns.Count
     rowsCount = Zone.Rows.Count
     '默认分隔符为 ;
     If Splitor = "" Then
         Splitor = ";"
     End If
     For i = 1 To columnCount Step 1
         For j = 1 To rowsCount Step 1
             If Zone.Cells(j, i) <> "" Then
                 '第一个匹配项，不添加分隔符
                 If resultStr <> "" Then
                     resultStr = resultStr & Splitor
                 End If
                 
                 resultStr = resultStr & Zone.Cells(j, i)
             End If
         Next
     Next
     ConnectString = resultStr
 End Function

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
 本文链接：https://blog.csdn.net/taller_2000/article/details/86713631
 VBA编程是经常会用到字符串拆分函数SPLIT，用法也不复杂，但是大家对于这个函数的参数未必完全了解，下面结合示例讲解一下参数的用法。
 
SPLIT函数的语法格式如下：
 
Split(expression, [ delimiter, [ limit, [ compare ]]])
 1
 参数 含义
 express 需要拆分的字符串
 delimiter 参数为拆分的分隔符，如果省略则使用空格做为分隔符
 limit 指定返回字符串的数量
 compare 指定拆分子字符串时的比较类型
 先来看一个示例，现在需要拆分Good good study day day up为单个单词。
 
Sub Demo1()
 Dim strString As String
 Dim varResult As Variant
 Dim arrResult() As String
 strString = “Good good study day day up”
 varResult = VBA.Split(strString)
 arrResult = VBA.Split(strString)
 End Sub
 1
 2
 3
 4
 5
 6
 7
 8
 VBA.Split(strString)省略delimiter参数，则使用空格做为分隔符，拆分为6个单词字符串，在【本地窗口】中可以查看数组的值，如下图所示。
 
请注意变量声明语句，用于保存结果的数组，可以使用如下两种方式：Variant变量或者字符串数组，但是不可用声明为Variant数组。
 
Dim varResult As Variant
Dim arrResult() As String
 
1
 2
 一般情况下，都无须指定LIMIT参数，下面看一个使用LIMIT参数的例子。对于一些国外地址如：888, Ocean Wind Rd, Markham, V4A，需要拆分为888，Ocean Wind Rd和Markham, V4A，而不是拆分为4段，此时就需要设置LIMIT参数为3。
 
Sub Demo2()
 Dim strString As String
 Dim arrResult() As String
 Dim i As Integer
 strString = “888, Ocean Wind Rd, Markham, V4A”
 arrResult = VBA.Split(strString, “,”, 3)
 For i = LBound(arrResult) To UBound(arrResult)
 Debug.Print Trim(arrResult(i))
 Next i
 End Sub
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10
 接下来看一下如何使用compare参数，下面示例代码中设置为vbTextCompare，即执行文本比较，也就是说忽略字符大小写区别（S和s都可以作为分界符）。如下代码结果为包含4个元素的字符串数组（AAA，BBB，CCC和DDD）。
 
Sub Demo3()
 Dim strString As String
 Dim arrResult() As String
 Dim i As Integer
 strString = “AAAsBBBSCCCsDDD”
 arrResult = VBA.Split(strString, delimiter:=“s”, compare:=vbTextCompare)
 For i = LBound(arrResult) To UBound(arrResult)
 Debug.Print Trim(arrResult(i))
 Next i
 End Sub
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10
 如果将compare参数设置为vbBinaryCompare，那么结果就会不同了。
 
Sub Demo4()
 Dim strString As String
 Dim arrResult() As String
 Dim i As Integer
 strString = “AAAsBBBSCCCsDDD”
 arrResult = VBA.Split(strString, delimiter:=“s”, compare:=vbBinaryCompare)
 For i = LBound(arrResult) To UBound(arrResult)
 Debug.Print Trim(arrResult(i))
 Next i
 End Sub
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10
 字符串被拆分为三段，BBB和CCC之间的S不再做为分界符。
 
AAA
 BBBSCCC
 DDD
 
通过这个讲解，希望大家完全明白了SPLIT函数的用法。
 ————————————————
 版权声明：本文为CSDN博主「taller_2000」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
 原文链接：https://blog.csdn.net/taller_2000/article/details/86713631