從妳的例子看,數字都在前面,漢字集中在後面,可以這樣做:
C1=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9","")
B1=SUBSTITUTE(A1,C1,"")
選定B1:C1
下拉
如圖:
這個方法的原理是,在原字符串中把數字都替換成"",剩下的就是漢字字符串了。
假定原字符串中漢字都集中在壹起,在原字符串中,把集中在壹起漢字替換成"",剩下的就是數字字符串了。
用如下VBA程序,則即使數字與漢字混雜在壹起都能分離開來。為了不把B列和C列的公式沖掉,我把運行結果放在D列和E列:
Sub 分離數字與漢字()
Dim rng As Range, a$, b$, i%
For Each rng In Intersect(UsedRange, Range("A:A"))
a$ = "'"
b$ = ""
For i% = 1 To Len(rng.Value)
If Mid(rng.Value, i%, 1) Like "[0-9]" Then
a$ = a$ & Mid(rng.Value, i%, 1)
End If
If Asc(Mid(rng.Value, i%, 1)) < 0 Then
b$ = b$ & Mid(rng.Value, i%, 1)
End If
Next i%
rng.Offset(, 3).Value = a$
rng.Offset(, 4).Value = b$
Next
End Sub