vba求字符里面符号数量

vba求字符里面符号数量

Sub Test()
S = "adfa:sd:sdfds，:sadf,asdf:Sss，，sdf：sdf"
MsgBox "英文冒号个数：" & UBound(Split(S, ":", , 0)) + 1
MsgBox "中英文冒号个数：" & UBound(Split(S, ":", , 1)) + 1
End Sub

用函数就会...
全是数组公式,需按CTRL+SHIFT+ENTER三键结束才可以.
求冒号个数
=SUM(IF(MId(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=":",1))
读最后一个冒号后面的字符
=RIGHt(A1,LEN(A1)-MAX(IF(MId(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=":",ROW(INDIRECT("1:"&LEN(A1))))))
---------------------------------------------
终于解决!!!
VBA求冒号个数
Sub aa()
x = 0
For i = 1 To Len([A1])
If Mid([A1], i, 1) = ":" Then
x = x + 1
End If
Next i
[B3] = x
End Sub
代码是将结果返回到B3单元格中
VBA求第二个问题
Public Sub bb()
For i = 1 To Len([a1])
If Mid([a1], i, 1) = ":" Then
[b3] = Right([a1], Len([a1]) - i)
End If
Next i
End Sub
代码结果返回到B3单元格中

按Alt+F11，在左边选择相应的表，然后把以下代码粘贴上：
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
If Target <> "" Then
fengefu = InputBox("请输入分隔符(请注意区分中英文)", "分隔符输入", ":")
If fengefu <> "" Then
fenge = Split(Target, fengefu)
geshu = UBound(fenge) + 1
lastone = fenge(UBound(fenge))
MsgBox "共有" & geshu & "个“" & fengefu & "”"
MsgBox "最后一个“" & fengefu & "”后的字符为" & lastone
End If
End If
End If
End Sub
在表中单击相应的单元格即可~