第1个回答 推荐于2016-07-24
VBA代码如下(附件是文件样本):
Function mm(ByVal rg As Range, G As Integer) As Double
Dim regExp As Object, m, mAry
Set regExp = CreateObject("VBscript.regExp")
With regExp
.Global = True
.Pattern = "\d|\*|\."
For Each m In .Execute(rg.Value)
s = s & m
Next
End With
mAry = Split(s, "*")
mm = mAry(G - 1)
End Function
本回答被提问者采纳
第2个回答 2015-02-12
会使用VBA吗?
如果会的话把下方代码添加到模块中~
Function GetLWH(ByVal cell As Range, ByVal kind As Integer)
Dim oRegExp As Object
Dim oMatches As Object
Set oRegExp = CreateObject("vbscript.regexp")
With oRegExp
.Global = True
.IgnoreCase = True
.Pattern = "[^0-9.][0-9.]+"
Set oMatches = .Execute(cell.Formula)
End With
If kind > oMatches.Count Then
s = oMatches(oMatches.Count - 1)
Else
s = oMatches.Item(kind - 1)
End If
GetLWH = Right(s, Len(s) - 1)
Set oRegExp = Nothing
Set oMatches = Nothing
End Function
再在表中如下输入:
C1=GetLWH(B1,1)
D1=GetLWH(B1,2)
E1=GetLWH(B1,3)
然后下拉填充
第3个回答 2015-02-12
C2输入公式=TRIM(MID(SUBSTITUTE(B2,"*",REPT(" ",10)),FIND(" ",B2),5))
D2输入公式=TRIM(MID(SUBSTITUTE(B2,"*",REPT(" ",10)),FIND(" ",B2)+10,5))
E2输入公式=SUBSTITUTE(SUBSTITUTE(MID(B2,FIND("*",B2)+1+LEN(D2)+1,50),"CM",""),"MM","")
选中公式单元格,下拉公式即可。