日期: 2021 年 3 月 22 日

有时候我们经常遇到单元格中既有数字又有字母、汉字等其他字符，怎么把它们分别提取出来呢？
下面用EXCEL vba来实现：

Sub cf()
‘定义变量：i(表格中最后一行），rng(遍历要提取的每一个单元格）
Dim rng As Range
Dim s As String
Dim n As String
i = Range(“a1”).End(xlDown).Row
Range(“b2:c” & i).ClearComments ‘初始化清空单元格区域
[b1] = “姓名”
[c1] = “数字”
For Each rng In Range(“a2”, “a” & i)
For x = 1 To Len(rng)
If Mid(rng, x, 1) Like “[a-z]” Or Mid(rng, x, 1) Like “[A-Z]” Or Mid(rng, x, 1) Like “[一-龢]” Then
s = s + Mid(rng, x, 1)
Else
n = n & Mid(rng, x, 1)
End If
Next
Range(“b” & rng.Row) = s
Range(“c” & rng.Row) = n
n = “”
s = “”
Next
End Sub

最后添加一个按钮执行此段代码即可。