VBA，群里求助的例子，如何对字符串进行分列，取出身份证号

1问题：想分离出身份证号

 

2 VBA解决

2.1代码1：

• 用数组承接
• 两次数组，分别 split()

Sub test3()
Dim arr1()
Dim arr2()
k = 1
j = 1


a = Range("c65536").End(xlUp).Row


ReDim arr1(1 To a)
For Each m In Range("c1:c" & a)
    arr1(k) = Split(m, Chr(10))(1)
    Debug.Print arr1(k)
    k = k + 1
Next

ReDim arr2(1 To a)
For Each n In arr1
   arr2(j) = Split(n, "|")(0)
   Debug.Print arr2(j)
   j = j + 1
Next

End Sub

 

2.2 代码2

• 不用中间数组
• 直接两次split()
• 放入excel的某列

Sub test4()
k = 1
a = Range("c65536").End(xlUp).Row
For Each m In Range("c1:c" & a)
    Cells(k, 4) = Split(Split(m, Chr(10))(1), "|")(0)
    k = k + 1
Next
End Sub

 

 

3 其他办法

3.1 公式方法

• =MID(C2,11,30)
• left(right())
• find()
• substitute()