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