A表a列是“姓名空格籍贯空格身份证号码”,写Vba 代码将a列按空格分列成abc三列,注意避免全是数字的身份证号码后面几位会变成0,
时间: 2023-05-11 22:02:49 浏览: 55
以下是 VBA 代码实现:
Sub SplitNames()
Dim cell As Range
Dim fullName As String
Dim nameParts() As String
For Each cell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
fullName = cell.Value
nameParts = Split(fullName, " ")
If UBound(nameParts) = 2 Then
cell.Offset(0, 1).Value = nameParts(0)
cell.Offset(0, 2).Value = nameParts(1)
cell.Offset(0, 3).Value = nameParts(2)
ElseIf UBound(nameParts) = 3 Then
cell.Offset(0, 1).Value = nameParts(0)
cell.Offset(0, 2).Value = nameParts(1) & " " & nameParts(2)
cell.Offset(0, 3).Value = nameParts(3)
End If
Next cell
End Sub
这段代码会将 A 列中的每个单元格按空格分成三列,分别是姓名、籍贯和身份证号码。如果身份证号码后面几位全是 0,也会保留这些 0。