I need to count the number of times a particular letter is in the third place of a string of letters and numbers. These strings are in Column A to M, rows 1 to 650. I have tried combining = Countif and = Mid in different ways but have been unsuccessful.
Thank you.
Brian W
Try this, it's a bit of a "cheat",
as it uses both =COUNTIF() and Wild Card characters.It seems to work, but I have not done extensive testing:
=COUNTIF(A1:M650,"??X*")
The X is the single character, in the Third position that your looking for,
the first two ? characters are the wild card for Any Single Character,
the trailing * means Any Character, Any Number of Times.As best as I can tell this only works for TEXT,
if the cells your checking are ALL NUMBERS then it above will not work,
or at least I could not get it to work with ONLY numbers.
I tried a couple of array formulas, but nothing seemed to work consistently. If you don't mind a User Defined Function, this should work:
Function countD(ByVal rng As Range) Application.Volatile For Each cell In rng If Mid(cell, 3, 1) = "d" Then tmpCount = tmpCount + 1 End If Next countD = tmpCount End FunctionAfter pasting this code into a standard VBA module, enter =countD(A1:M650) in any cell and it will count each "3rd position d" in the range entered.
If it will always be A1:M650, then use this and just enter =countD()
Function countD() Application.Volatile For Each cell In Range("A1:M650") If Mid(cell, 3, 1) = "d" Then tmpCount = tmpCount + 1 End If Next countD = tmpCount End FunctionClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.
Dear DerbyDad03, Thank you for your response. I have never used a User Defined Function so I do not know how to enter the code that you provided. Can you give me any references so that I can learn how to use it?
Thanks again,
Brian W
Look here: http://office.microsoft.com/en-us/e...
It's more than you probably need, but it gives a pretty complete picture of UDF's.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
Try this, it's a bit of a "cheat",
as it uses both =COUNTIF() and Wild Card characters.It seems to work, but I have not done extensive testing:
=COUNTIF(A1:M650,"??X*")
The X is the single character, in the Third position that your looking for,
the first two ? characters are the wild card for Any Single Character,
the trailing * means Any Character, Any Number of Times.As best as I can tell this only works for TEXT,
if the cells your checking are ALL NUMBERS then it above will not work,
or at least I could not get it to work with ONLY numbers.
DerbyDad03, Thank you for your assistance; I will read the info you cited about User Defined Functions and will try to use them the next time I run into a similar problem.
Brian W
Dear Mike, Thank you for your assistance. The strings that I am using contain text and your solution worked perfectly.
Brian W