A列中存在如下字符
4F
5P
7DDP
86F
486DP
。。。
每个字符串中数字个数不确定，英文字母个数也不确定。
如何能将数字和英文字母分成2列？
求VBA方法。
我也来凑个热闹。
下面代码 可以 分离：数字+字母，字母+数字，纯数字，纯字母。
Sub AAA()Dim Ar, Br
Dim i As Long
Dim J As Long
Ar = Range("A1:A" & Range("A" & Rows.Count).End(3).Row)
ReDim Br(1 To UBound(Ar), 1 To 2)
For i = 1 To UBound(Ar)
For J = 1 To Len(Ar(i, 1))
If Not IsNumeric(Mid(Ar(i, 1), J, 1)) Then Exit For
Next
If J = 1 Then
For J = 1 To Len(Ar(i, 1))
If IsNumeric(Mid(Ar(i, 1), J, 1)) Then Exit For
Next
End If
Br(i, 1) = Left(Ar(i, 1), J - 1)
Br(i, 2) = Mid(Ar(i, 1), J)
Next
[B1].Resize(UBound(Br), 2) = Br
End Sub
温馨提示：答案为网友推荐，仅供参考
第1个回答 2013-07-24
sub abc()
dim rng as range
for each rng in range(range("A1"), range("A65536").end(xlup))
d=""
s=""
for i = 1 to len(rng.text)
if mid(rng.text, i, 1)<="0" then
d = d & mid(rng.text, i, 1)
else
s = s & mid(rng.text, i, 1)
end if
next i
rng.offset(0,1) = d
rng.offset(0,2) = s
next rng
end sub
dim rng as range
for each rng in range(range("A1"), range("A65536").end(xlup))
d=""
s=""
for i = 1 to len(rng.text)
if mid(rng.text, i, 1)<="0" then
d = d & mid(rng.text, i, 1)
else
s = s & mid(rng.text, i, 1)
end if
next i
rng.offset(0,1) = d
rng.offset(0,2) = s
next rng
end sub
数字保存在B列，字母保存在C列
执行结果没有分开，因为if mid(rng.text, i, 1)<="0" then这句好像写的不对，d一致都为空。
第2个回答 2013-07-24
Sub AAA()
Dim Len_Rn As Integer
Dim n As Integer
Dim m As Long
Dim X As Long
Dim flg As Boolean
X = ActiveSheet.Range("A65536").End(xlUp).Row
Len_Rn = Len(ActiveSheet.Range("A1"))
For m = 1 To X
n = 1
flg = False
Do While flg = False
If Not IsNumeric(Mid(ActiveSheet.Range("A" & m), n, 1)) Then
ActiveSheet.Range("B" & m) = Mid(ActiveSheet.Range("A" & m), 1, n - 1)
ActiveSheet.Range("C" & m) = Mid(ActiveSheet.Range("A" & m), n, Len_Rn)
flg = True
End If
n = n + 1
Loop
Next m
End Sub
Dim Len_Rn As Integer
Dim n As Integer
Dim m As Long
Dim X As Long
Dim flg As Boolean
X = ActiveSheet.Range("A65536").End(xlUp).Row
Len_Rn = Len(ActiveSheet.Range("A1"))
For m = 1 To X
n = 1
flg = False
Do While flg = False
If Not IsNumeric(Mid(ActiveSheet.Range("A" & m), n, 1)) Then
ActiveSheet.Range("B" & m) = Mid(ActiveSheet.Range("A" & m), 1, n - 1)
ActiveSheet.Range("C" & m) = Mid(ActiveSheet.Range("A" & m), n, Len_Rn)
flg = True
End If
n = n + 1
Loop
Next m
End Sub
第3个回答 2013-07-24
基本上只能数字固定个数，或者是跟数字相连的英文字母为同一个，才能做批量分离
第4个回答 2013-07-24
按你提供的实例规律，用函数就可分离
大哥。。。谢谢你的函数。但是。。。适用性较小，因为数字个数和字母个数都不确定。
