如何分列不规则数字和文字

sb1489321

表A列中不规则数字和文字，如何快速分开

sb1489321

高手都潜水了吗？

solextrade

B3 =LEFT(A3,MIN(LOOKUP(1,0/(CODE(MID(A3,ROW($1:$28),1))<99)/(CODE(MID(A3,ROW($2:$29),1))>99),ROW($1:$28)),FIND("号",A3&"号"))+COUNTIF(A3,"*号*"))
C3 =SUBSTITUTE(SUBSTITUTE(A3,B3,),D3,)
D3 =-LOOKUP(,-RIGHT(A3,ROW($1:$9)))

我走我流

公 式  写 不出 来。。。 抄 段 V B A

sb1489321

感谢3楼solextrade，问题得已解决，同时感谢4楼我走我流

准提部林

D3//
=-LOOKUP(,-RIGHT(A3,ROW($1:$19)))

B3//數組
=LEFT(A3,MATCH(1,-MID(SUBSTITUTE(LEFT(A3,LEN(A3)-LEN(D3)),"号",1),ROW($1:$39),1)))

C3//
=MID(LEFT(A3,LEN(A3)-LEN(D3)),LEN(B3)+1,19)

jivy1212

1. =LEFT(A3,LARGE(IF(LENB(MID(SUBSTITUTE(A3,"号"," "),ROW($1:$66),2))=3,ROW($1:$66)),2))

复制代码

1. =SUBSTITUTE(LEFTB(A3,SEARCHB("?",A3,LENB(B3)+1)-1),B3,)

复制代码

1. =MIDB(A3,SEARCHB("?",A3,LENB(B3)+1),66)

复制代码

凑数来的

橒♂蝣

b3:  =MID( LEFT(A8,11), 1,11-MIN(IF(MMULT(((LEFT(RIGHT(LEFT(A8,11),ROW($1:$6)+{0,1}),1)>"9")+(LEFT(RIGHT(LEFT(A8,11),ROW($1:$6)+{0,1}),1)<"0"))/1,{10;1})=10,ROW($1:$6)))
c3: =LET(y,MID(RIGHT(A3,9),10-MAX(IF((LEFT(RIGHT(RIGHT(A3,9),ROW($1:$9)),1)>"9")/1=1,ROW($1:$9))),COUNT(IF(((LEFT(RIGHT(RIGHT(A3,9),ROW($1:$9)),1)>"9"))/1=1,ROW($1:$9)))),
X,RIGHT(y,3),
IF(ISERROR(FIND("号",X)),X,SUBSTITUTE(X,"号","")))
d3:  =RIGHT(       RIGHT(A3,9),       9-MIN(IF(MMULT(((LEFT(RIGHT( RIGHT(A3,9),ROW($1:$9)+{0,1}),1)>"9")+(LEFT(RIGHT( RIGHT(A3,9),ROW($1:$9)+{0,1}),1)<"0"))/1,{10;1})=10,ROW($2:$10)))
           )

249773986

Sub 分组匹配()
Dim reg As Object, ss, ss1, s
Set reg = CreateObject("vbscript.regexp")
With reg
    .Global = True
    .Pattern = "(.+[号记\d])([一-龢]+)(\d+\.?\d*$)"
    For Each ss In Range("a3", Cells(Rows.Count, 1).End(xlUp))
        Set ss1 = .Execute(ss)
        For Each s In ss1
            ss.Offset(0, 1) = s.SubMatches(0)
            ss.Offset(0, 2) = s.SubMatches(1)
            ss.Offset(0, 3) = s.SubMatches(2)
        Next s
    Next ss
End With
End Sub