excel數空格問題

TH30

想計A列，A2至A10之間，有幾多格是有字的

用COUNTA(A2:A10)
如果在A3打SPACEBAR，EXCEL會視作有字，而非空格

有辦法數 '真正有字' 而略去不計 '只有SPACE的偽空格' 的格數嗎？

edit :
原po說得不清楚，我是想計算 '真正' 有字的CELL

asp123

Count Blank =COUNTBLANK(A2:A10)

TH30

Count Blank =COUNTBLANK(A2:A10)
asp123 發表於 2018-11-13 10:27 AM

COUNTBLANK 一樣計 SPACE
正是我面對的問題

poon120403

=COUNTA(A2:A10)-COUNTIF(A2:A10,"* *")

但係A2:A10入面唔可以包含space bar

clio

B Column "=trim(A1)"

COUNTA(B1:B10) - COUNTBLANK(B1:B10)

傑洛

deleted

ThomasYau

=COUNTIF(A2:A10,"> ")+COUNTIF(A2:A10,">0")
咁樣得唔得?

tak5tak

Add column B as helper column
之後Cell B2-B10 打下面formula去計column A characters 數量.
=LEN(SUBSTITUTE(A2," ",""))

之後 use countif > 0

ThomasYau

=COUNTIF(A2:A10,"> ")+COUNTIF(A2:A10,">0")
多過一個SPACE 又唔得
=COUNTIF(A2:A10,">"&REPT(" ",254))+COUNTIF(A2:A10,">0")
多過254個SPACE 又唔得

用Data Validation, 唔比入Space 就唔使搞咁多野

ThomasYau

或者自己加 function
Function CountNotBlank(Arg1 As Range) As Double
    For Each elem In Arg1
        If Trim(elem.Value) <> "" Then
            CountNotBlank = CountNotBlank + 1
        End If
    Next elem
End Function