How do you count a text in a column.
example in column d Range d9:d50 how many times does is the letter p entered.
I am sure there is an internal excel formula for this (countif doen't work on text) (count doesn't work on text)
any body know the correct FUNCTION Name to complete this task.
mike in wisconsin
Hi
How about
[vba]
=countif(d9:d50,"*"&"p"&"*")
[/vba]
Tony
That counts the number of cells that contain at least one "p", it doesn't count the total number of "p"s when there's more than one in a cell. EDIT: Oops sorry, neither does this... Hmmmm - you'd then need to use Split to count the number of p's in each cellOriginally Posted by acw
[vba]Option Explicit
'
Sub FindPee()
'
Dim Cell As Range, FirstAddress As String, N As Long
'
With Range("D950")
Set Cell = .Find("p", LookIn:=xlValues)
If Not Cell Is Nothing Then
FirstAddress = Cell.Address
Do
N = N + 1
Set Cell = .FindNext(Cell)
Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
End If
End With
'
MsgBox N
'
End Sub[/vba]
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Here's the split pea solution
[vba]
Sub FindPea()
Dim pod As Range, peas As Long
For Each pod In Range("D9
If UBound(Split(pod, "p")) <> -1 Then
peas = peas + UBound(Split(pod, "p"))
End If
Next
MsgBox peas
End Sub
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Perhaps for an exact match on lower case p only:
[vba]=SUMPRODUCT(LEN(D9
or for either capital or lowercase p:
[vba]
=SUMPRODUCT(LEN(D9
[/vba]
Last edited by mdmackillop; 06-16-2006 at 01:15 AM. Reason: "D" missing from formulae
Ingenious
Must get to know more about SUMPRODUCT.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Just notice Hotpepper post.
=SUMPRODUCT((LEN(D9:D50)-LEN(SUBSTITUTE(UPPER(D9:D50),"P",""))
Last edited by Shazam; 06-16-2006 at 07:04 AM.
Solved with 3 answers.
the =countif(d9:d50,"*"&"p"&"*") provided the best results because it counted number of blocks with a "p" in them and that the answer I needed.
theCounted all occurances of "P" in the Blocks not exactly what I needed but close. Also after the =sum formula is entered you must "Ctrl+Shift+Enter" to get it to work. MS KB 213889=SUMPRODUCT((LEN(D9:D50)-LEN(SUBSTITUTE(UPPER(D9:D50),"P",""))
I did not try the VB solution because I new at VB.
Thanks for all your help
Mike in Wisconsin
Posting Permissions
Reply With Quote
