Hi all,
How can I count cells containing Text only?

TIA
Soniya

Soniya

the code below will count the cells with constants. (Not formulas, blank,
etc) in a selection

Sub CountConstants()
Dim lConstantCount As Long
lConstantCount = Selection.SpecialCells(xlCellTypeConstants).Count
MsgBox "There are " & lConstantCount & " Cells with constants in the
selection"
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Soniya" wrote in message
...
Hi all,
How can I count cells containing Text only?

TIA
Soniya

With your data in A1:A100
In B1 enter:
=--ISTEXT(A1) and copy down
Then Sum(B1:B100)

"Soniya" wrote in message
...
Hi all,
How can I count cells containing Text only?

TIA
Soniya

Perhaps you are looking for a programming answer and not a function answer,
anyway this single **Array-entered** formula is better. Use Ctrl+Shift+Enter
after
keying or pasting in:

=SUM(ISTEXT(A1:A100)*ISTEXT(A1:A100))

HTH

"JMay" wrote in message
news:oGrmb.101435$AH4.53542@lakeread06...
With your data in A1:A100
In B1 enter:
=--ISTEXT(A1) and copy down
Then Sum(B1:B100)

"Soniya" wrote in message
...
Hi all,
How can I count cells containing Text only?

TIA
Soniya

Nick, think you meant to include the second argument as well:

Sub CountConstants()
Dim lConstantCount As Long
On Error Resume Next
lConstantCount = Selection.SpecialCells( _
xlCellTypeConstants,xlTextValues).Count
On Error goto 0
MsgBox "There are " & lConstantCount _
& " Cells with constants in the
selection"
End Sub

Just in case there are constant numbers in the selection as well.

--
Regards,
Tom Ogilvy


Nick Hodge wrote in message
...
Soniya

the code below will count the cells with constants. (Not formulas, blank,
etc) in a selection

Sub CountConstants()
Dim lConstantCount As Long
lConstantCount = Selection.SpecialCells(xlCellTypeConstants).Count
MsgBox "There are " & lConstantCount & " Cells with constants in the
selection"
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Soniya" wrote in message
...
Hi all,
How can I count cells containing Text only?

TIA
Soniya