excel中如何将同一单元格数字跟汉字分别放在两个单元格里?
从你的例子看,数字都在前面,汉字集中在后面,可以这样做:
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