![](https://iknow-base.cdn.bcebos.com/lxb/notice.png)
EXCEL单元格内有字符怎样统计个数?
如果一个单元格内有以上数值,怎么能够自动在另一个单元格内输出数量?
上面这个数量是21个,C114-115相当于C114 C115 展开
看似简单,做起来还蛮费神的,OK了,假如数据在A1单元格,在C1单元格输入公式=(LEN(A1)-LEN(SUBSTITUTE(A1,"C","")))-(LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))+SUMPRODUCT(MID(A1,FIND("@",SUBSTITUTE(A1,"-","@",ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))))))+1,FIND("@",SUBSTITUTE(A1,"-","@",ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))))))+3-FIND("@",SUBSTITUTE(A1,"-","@",ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))))))-MID(A1,FIND("@",SUBSTITUTE(A1,"-","@",ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))))))-3,(FIND("@",SUBSTITUTE(A1,"-","@",ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))))))-(FIND("@",SUBSTITUTE(A1,"-","@",ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))))))-3)))+1)
宏也可以,能算出来就行。如果可以解决,还可以提高悬赏。每个特定的字符之间是用一个空格间隔。比如C114-115 C214-215,中间是一个空格。
自定义函数:
Public Function CountX(Rng)
Dim Reg, mh, mhK, i As Long, L As Long
Set Reg = CreateObject("vbscript.regexp")
Reg.Pattern = "\d+\-*\d*"
Reg.Global = True
Set mh = Reg.Execute(Rng)
For Each mhK In mh
If InStr(mhK.Value, "-") > 0 Then
i = i + Abs(Evaluate(mhK.Value)) + 1
Else
i = i + 1
End If
L = L + 1
Next
CountX = i
End Function